Flag outliers in Excel when values fall outside a specific range

  • Thread starter Thread starter steves1080
  • Start date Start date
  • Tags Tags
    Excel
Join the discussion
Registration is free. Ask a follow-up in this thread, or start your own.
Sign up Log in
2 replies · 2K views
steves1080
Messages
64
Reaction score
1
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!
 

Discussion

Physics news on Phys.org
"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")
 
Last edited:
Yes, cell by cell works much better. Thanks for your input!