The IF function
 

Trying to use the IF function to get a cell to generate a "grade" based on a
number of points, e.g.:

IF the contents of B1 are less than 40, then B2 will read "A"
IF the contents of B1 are greater than or equal to 41, BUT less than 71,
then B2 will read "B"
IF the contents of B1 are greater than or equal to 71, then B2 will read "C".

Can't get this to work. Am currently using three cells with IF formula, with
the idea that they'll either read A or xxx, B or xxx or C or xxx (to
accommodate the true or false requirements of the IF function), but I think
I'm going about it ALL wrong.

What I really want to do, basically, is get a formula that reads the number
in a single cell, and gives me one of three letter values in another cell
based on the numerical value of the first cell.

This is driving me **bats**.

Any suggestions?

if(B1<40;"A";if(AND(B1=41;B<71);"B";"C"))


This should work for you...


--
ehntd
------------------------------------------------------------------------
ehntd's Profile: http://www.excelforum.com/member.php...o&userid=15865
View this thread: http://www.excelforum.com/showthread...hreadid=277667

On Fri, 12 Nov 2004 03:26:01 -0800, "Minesalargejackdaniels"
m wrote:

Trying to use the IF function to get a cell to generate a "grade" based on a
number of points, e.g.:

IF the contents of B1 are less than 40, then B2 will read "A"
IF the contents of B1 are greater than or equal to 41, BUT less than 71,
then B2 will read "B"
IF the contents of B1 are greater than or equal to 71, then B2 will read "C".

Can't get this to work. Am currently using three cells with IF formula, with
the idea that they'll either read A or xxx, B or xxx or C or xxx (to
accommodate the true or false requirements of the IF function), but I think
I'm going about it ALL wrong.

What I really want to do, basically, is get a formula that reads the number
in a single cell, and gives me one of three letter values in another cell
based on the numerical value of the first cell.

This is driving me **bats**.

Any suggestions?

You leave undefined the situation where B1=40. I have assumed that that grade
will be a "A". If that assumption is incorrect, you will need to change the
formula.

=IF(B1<41,"A",IF(B1<71,"B","C"))

Of course, this will also give an "A" if there is no entry in B1. You may want
to test for that, but how to test depends on how the points gets into B1. If
they are entered directly, and/or if the points must always be greater than
zero, then you can test for a number greater than 0.

If the number is computed by a formula, then the testing might depend on the
formula used. But something like:

=IF(B1="","",IF(B1<41,"A",IF(B1<71,"B","C")))

Finally, nesting IF's (or any function) can only be done to seven levels. So
if you are looking to expand this, take a look at the VLOOKUP function.



--ron

Hi

=CHOOSE(MATCH(B1,{1,41,71},1),"A","B","C")

(Values in B1 must be 0, otherwise an error is returned)


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Minesalargejackdaniels" m
wrote in message ...
Trying to use the IF function to get a cell to generate a "grade" based on
a
number of points, e.g.:

IF the contents of B1 are less than 40, then B2 will read "A"
IF the contents of B1 are greater than or equal to 41, BUT less than 71,
then B2 will read "B"
IF the contents of B1 are greater than or equal to 71, then B2 will read
"C".

Can't get this to work. Am currently using three cells with IF formula,
with
the idea that they'll either read A or xxx, B or xxx or C or xxx (to
accommodate the true or false requirements of the IF function), but I
think
I'm going about it ALL wrong.

What I really want to do, basically, is get a formula that reads the
number
in a single cell, and gives me one of three letter values in another cell
based on the numerical value of the first cell.

This is driving me **bats**.

Any suggestions?