If/Then Over a Column

**ScottL** · 02-21-2011, 11:19 AM

Hello, I am working with a very large data set and I want to set an if/then statement to check for erroneous values. I have a column of temperature data (C8:C31300) which I want to check for values above 95. I have this formula which doesn't work.

=IF(C8:C31300>95,"Out of Range","Within Range")

What am I doing wrong? Can you evaluate a column in this manner? I know you can do individual cells but that isn't reasonable for this set.

The overall goal is to write the if statement and have the date and time (columns A and B) appear so I can find when the error occurred.

Thanks.

Excel for Mac 2011

**Colin Legg** · 02-21-2011, 11:28 AM

Hi,

To use that you would have have to use an array range.

Two other immediate choices would be to put this formula in a free column (say D8) and then fill down:

=IF(C8>95,"Out of Range","Within Range")

Or, if you want a quick count of how many are out of range then you could use:

=COUNTIF(C8:C31300,">95")

**Cutter** · 02-21-2011, 11:43 AM

To achieve your end goal you could place the formula as suggested by Colin Legg but have it return a count of the number of previous entries that are out of range plus 1.

Then have an Index/Match formula to return dates/times based on the count number you input - ie: input a 1 and first instance date/time returns - input a 2 and second instance, etc.

Why do you say it isn't reasonable to use formulas for individual cells?

**ScottL** · 02-21-2011, 12:14 PM

It just isn't reasonable to analyze the data line-by line to find where it says the data is out of range. Could you help me out with equation for assigning ascending value to each "out or range" parameter? I like the idea of listing them sequentially and then find the date/time for each one. Thanks.

**scottylad2** · 02-21-2011, 12:19 PM

Can you upload a sample of what you have and what you want returned?

**ScottL** · 02-21-2011, 12:47 PM

Attached a sample of the Data. I would like to find out where/if in the data set Temperature values are above 95 and where Battery is below 4 or above 20. When a value is out of range I would like to be able to have the date and time pull up. Similar to the max and min stuff you will find on the second sheet.

Thanks.

**Cutter** · 02-21-2011, 01:10 PM

Here is how you can get the out of range temperature info.

Your sample didn't contain any out of range temperatures so I changed one to demonstrate.

The 95 is held in a cell (M7) so it can be varied without changing formulas. The instance # is held in cell N3. To find instance #1 you input 1, etc.

Formulas not yet constructed to eliminate error returns.