×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Filtering out non number values

Filtering out non number values

Filtering out non number values

(OP)
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!.

RE: Filtering out non number values

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.

RE: Filtering out non number values

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

RE: Filtering out non number values

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.

RE: Filtering out non number values

(OP)
I am using excel 2000.

Formatting did not help.

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

RE: Filtering out non number values

(OP)
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.

RE: Filtering out non number values

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.

RE: Filtering out non number values

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

RE: Filtering out non number values

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!

RE: Filtering out non number values

(OP)
Aviat You Rule!!!  Have a star.

Just waht I was needing to know.  Thanks a bunch

RE: Filtering out non number values

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close