Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Help with MS Excel equation

  1. May 27, 2014 #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!
     
  2. jcsd
  3. May 27, 2014 #2
    "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: May 28, 2014
  4. May 29, 2014 #3
    Yes, cell by cell works much better. Thanks for your input!
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Help with MS Excel equation
  1. I need help with Excel (Replies: 2)

Loading...