IF THEN Statements

**mike1199** · 05-15-2010, 12:20 PM

Hello,

I have a data range C2:2001 in a worksheet titled "STATS" and within each cell in the range (C2:2001) is a value of "TRUE" which identfies an outlier and the word "FALSE" that does not identify an outlier.

Problem:

In another worksheet title "Data Entry" I cannot figure out a IF Then statement which works. In the worksheet titled "Data Entry" I need to be identified by the word "ERROR" if any cell in the worksheet "STATS" within the data range C2:2001 contains the word "TRUE".

Seems not to difficult but just won't work.

The cells starting in C2 contain the following formula: =ABS(B2)>3 if B2 is more than 3 standard deviations from the Mean, the cell (C2) will read "TRUE".

All help is greatly appreciated.

Thank You,

Michael

**DonkeyOte** · 05-15-2010, 12:34 PM

The TRUE you're referring to is a Boolean rather than a text string so you should test based on =TRUE rather than ="TRUE", eg:

=IF(STATS!C2=TRUE,"ERROR","")

If all cells really do contain only TRUE/FALSE Booleans then technically the =TRUE isn't required given the value being tested is itself a Boolean, ie:

=IF(STATS!C2,"ERROR","")

would do the same.

**mike1199** · 05-15-2010, 01:05 PM

DonkeyOte

Thanks for the quick reply. Your solution works for an individual cell, but I need to check a range of cells C2:C2001. The outlier may or may not reside in C2.

The formula:
=IF(Stats!C2:2001,"ERROR","")
Does not pick up an outlier in cell C20.

The formula:
=IF(Stats!C20,"ERROR","")
Works great but only for that particular cell. Do I have to enter the same formula 2,000 times for each individual cell?

Any help is appreciated.

**Cheeky Charlie** · 05-15-2010, 01:12 PM

In DO's absence, what do you want to know?

ERROR/blank in substitution for TRUE/FALSE for every cell seems somewhat arbitrary. You could use =countif(<range>,true) to count all 'true's.

Perhaps post an example of what you're looking for?

CC

PS
A specific answer to yoru question is 'no, you can copy and paste the formula once for as many cells as you please' - but this may sound facetious if that's not really the question you meant...