Filtering out non number values

Question

Filtering out non number values

JEB66 (Mechanical)

(OP)

26 Aug 04 13:00

I use excell to sort and average some data collected via OSI's plant information system.

Ocasionally, there will be a problem with the server or the instruments which caused the archived value to read "invalid" or "bad data" or "no good data", whatever the divice is configured to read at those times.

I cannot use this to do any calculations because it is not a number. If there any function that will filter these cells out and replace them with a number or a null value?

Everything that I have tried comes up with #VALUE!.

analogkid2digitalman (Electrical) · Answer 1 · 2004-08-26 14:34:59

Won't formatting the cells as Number prevent this?

Wheels within wheels / In a spiral array
A pattern so grand / And complex
Time after time / We lose sight of the way
Our causes can't see / Their effects.

BillPSU (Industrial) · Answer 2 · 2004-08-26 14:36:58

Excel 2002 with SP3 will drop any non numeric data. What version of Excel are you using.

analogkid2digitalman (Electrical) · Answer 3 · 2004-08-26 14:40:36

In Excel 2000, I arbitrarily tested the AVERAGE and MAX functions with 2 cells, one filled with text, the other a number. Regardless of formatting (text or number for the text filled cell), the functions performed correctly, that is, they ignored the text in the calculations.

Wheels within wheels / In a spiral array
A pattern so grand / And complex
Time after time / We lose sight of the way
Our causes can't see / Their effects.

JEB66 (Mechanical) · Answer 4 · 2004-08-26 15:47:55

I am using excel 2000.

Formatting did not help.

For me average add subtract if whatever still returns #VALUE!

JEB66 (Mechanical) · Answer 5 · 2004-08-26 15:51:31

analog, I did the same test as you did and it worked like you said.

I guess this tells us that the non numerical values I am getting from the PI servers is not text.

analogkid2digitalman (Electrical) · Answer 6 · 2004-08-26 17:30:52

Jeb66:

If there are any cells with #N/A or #VALUE, they will propagate throughout the the calculation. For example, AVERAGE on cells occupied with 5 and #VALUE, will return #VALUE. Could this be the case in your sheet?

I'm sure there is a means to filter offending cells via macro on built in function, I am just a loss right now for the solution.

aviat (Aerospace) · Answer 7 · 2004-08-26 22:38:16

Try:
=IF(ISNUMBER(E1:E6),AVERAGE(E1:E6),0)
Enter as an array formula (Ctrl+shift+enter)

melone (Electrical) · Answer 8 · 2004-08-27 00:07:10

1) Add a column and fill it with ascending numbers (starting at the beginning of your list, and end at the final value).

2) Select all of the cells and sort by 2 criteria: the data, and then by new column. This should collect all of the text, and still preserve the order of the data.

Hope that helps!

JEB66 (Mechanical) · Answer 9 · 2004-08-27 11:42:57

Aviat You Rule!!! Have a star.

Just waht I was needing to know. Thanks a bunch

InspEngr (Petroleum) · Answer 10 · 2004-08-27 12:43:19

The syntax I had to use to make Aviat's (excellent) suggestion work is

{=AVERAGE(IF(ISNUMBER(E1:E6),E1:E6,""))}

The brackets {} indicate the array formula when you type (CTRL+SHIFT+ENTER)

Keep in mind that if you put a zero (0) at the end of the IF portion, that zero is averaged into the calculation. Putting a null value ("") into the calculation causes it to average only the remaining values.

At least, this is what I had to do. Note: Microsoft Excel 2000 with SP-3.

-InspEngr

Come Join Us!

Posting Guidelines

Filtering out non number values