Identifying Outliers in MS Excel: How to Create a Formula for Numerical Ranges

[steves1080]

I have a row of numbers, and I want to create a formula that will look at the entire row, and then look at ONLY the filled-in cells (i.e. ignore blank cells), and then determine if any of the values in that row are inside a specific numerical range. If they are, great. If not, I will make a warning that notifies the user that there is an outlier.

Thanks for any help!

 

[skeptic2]

"I have a row of numbers,..."
Is this row of numbers in the same cell or numbers in a row of cells?

"...and I want to create a formula that will look at the entire row,..."
If the numbers are in multiple cells, do you want to look at them cell by cell or at all the cells at once?

"...and then look at ONLY the filled-in cells (i.e. ignore blank cells),..."
What is the purpose of looking at the entire row if you then want to look at it cell by cell?
If you want one formula to look at the numbers cell by cell, you'll probably need to write a macro. Otherwise why can't you copy one formula to each cell in a parallel row that examines (for instance) the cell above it for not being empty and for being within the range you specify?

Test Num. 3
Result great
Min 3
Max 7
=IF(LEN(B1)>0,IF(OR(B1<3,B1>7),"outlier","great"),"empty")

 

[steves1080]

Yes, cell by cell works much better. Thanks for your input!