Using IF statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scantor145 - 05 May 2006 15:39 GMT

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

Reply to this Message

Bob Phillips - 05 May 2006 15:44 GMT

=IF(COUNTIF(B2:B6,"<"&D1)>0,"Yes","No")

although that will return Yes in your example nit No.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Excel 2003
>
[quoted text clipped - 23 lines]
> If you had a column with 50 entries it would be tedious to specify each
> value in an IF statement.

Reply to this Message

Puppet_Sock - 05 May 2006 17:21 GMT

> Excel 2003
>
[quoted text clipped - 23 lines]
> If you had a column with 50 entries it would be tedious to specify each
> value in an IF statement.

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

Reply to this Message