Using IF statement

  • Thread starter Thread starter scantor145
  • Start date Start date
S

scantor145

Excel 2003

Trying to write an IF statement in a particular cell and don't know i
it's possible. I have the following example data in spoecified cells:

B2: 131
B3: 130
B4: 131
B5: 138
B6: 139

Target Value Cell C1: 136

Is it possible to write a statement, in say cell D1, such that that th
entire range of values B2 to B6 is compared to the target value in cel
C1. For example, if *ANY* value in B2:B6 is less than the target the
print "Yes", otherwise print "No".

So for the case above a "No" should be the result. Obviously, it ca
be done if the statement includes reference to every single value, B2
B3,....

Just wondering if there was a way to specify the entire range all a
once.

If you had a column with 50 entries it would be tedious to specify eac
value in an IF statement
 
=IF(COUNTIF(B2:B6,"<"&D1)>0,"Yes","No")

although that will return Yes in your example nit No.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
scantor145 said:
Excel 2003

Trying to write an IF statement in a particular cell and don't know if
it's possible. I have the following example data in spoecified cells:

B2: 131
B3: 130
B4: 131
B5: 138
B6: 139

Target Value Cell C1: 136

Is it possible to write a statement, in say cell D1, such that that the
entire range of values B2 to B6 is compared to the target value in cell
C1. For example, if *ANY* value in B2:B6 is less than the target then
print "Yes", otherwise print "No".

So for the case above a "No" should be the result. Obviously, it can
be done if the statement includes reference to every single value, B2,
B3,....

Just wondering if there was a way to specify the entire range all at
once.

If you had a column with 50 entries it would be tedious to specify each
value in an IF statement.
Click to expand...

As an alternative to Bob's method (which is perfectly fine) you could
also use the min function.

=if( min(b1:b6) < c1,"Yes","No")

Though the countif is probably more flexible in general.
Socks
 
Back
Top