Using AND or OR in a IF formula

[Guest]

I am trying to create a formula that will assign a letter to a cell depending
on the amount in the cell next to where the letter will end up.
In the numbers below if 2.0 is in cell C5 I want to assign cell D5 an A. If
1.3 is in cell C6, I want to assign a B to cell D6. My formula looks like
this:

=if($C$5>=c15,"A", $C$5>=c16 and <c15,"B", $C$5<c16, "C")

This formula is not working.

2.0 (C5)
1.3 (C6)
0.6 (C7)
0.9 (C8)
0.3 (C9)
1.8 (C10)
1.4 (C11)

1.5 A (C15 and D15)
1.0 B (C16 and D16)
0.7 C (C17 and D17)

Would appreciate suggestions. Thanks!

 

[Franz Verga]

I am trying to create a formula that will assign a letter to a cell
depending on the amount in the cell next to where the letter will end
up.
In the numbers below if 2.0 is in cell C5 I want to assign cell D5 an
A. If
1.3 is in cell C6, I want to assign a B to cell D6. My formula looks
like this:

=if($C$5>=c15,"A", $C$5>=c16 and <c15,"B", $C$5<c16, "C")

This formula is not working.

2.0 (C5)
1.3 (C6)
0.6 (C7)
0.9 (C8)
0.3 (C9)
1.8 (C10)
1.4 (C11)

1.5 A (C15 and D15)
1.0 B (C16 and D16)
0.7 C (C17 and D17)

Would appreciate suggestions. Thanks!

Try with this one:

=IF($C$5>=c15,"A", IF(AND($C$5>=c16, $C$5<c15),"B", IF($C$5<c16, "C","")))

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[joeu2004]

In the numbers below if 2.0 is in cell C5 I want to assign cell D5 an A. If
1.3 is in cell C6, I want to assign a B to cell D6. My formula looks like this:
=if($C$5>=c15,"A", $C$5>=c16 and <c15,"B", $C$5<c16, "C")

Here is what I believe you are trying to write:

=if($C$5>=C15, "A", if(and($C$5>=C16, $C$5<C15), "B", if($C$5<C16,
"C"))

But you are overspecifying the conditions. For example, you do not
need to test $C$5<C15 in the second IF() because that is implied by the
fact that $C$5>=C15 is false in the first IF(). So you could write:

=if($C$5>=C15, "A", if($C$5>=C16, "B", "C"))

That works fine for the 2 conditions that you mention. But if you
really have several more conditions, you might consider alternatives to
using IF(). Take a look at VLOOKUP().

 

[Guest]

thanks a bunch. I played with the formula. Found out I needed absolutes in
the opposite place I had thought. Turned these around in the shorter formula
you posted and it works. Thanks, again! I have had such good luck with
several problems using this group.