Using IF statement

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
 
B

Bob Phillips

=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)
 
P

Puppet_Sock

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top