Can anyone tell me why this IF formula isn't working?

=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!

MelissaS wrote:
Can anyone tell me why this IF formula isn't working?

=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!




You can't use multiple comparisons that way. Try it like this:

=IF(AND(D5<E5,E5<F5),"A",IF(AND(...

TRy

=IF(AND(D5<E5,E5<F5),"A",
IF(AND(D5E5,E5F5),"B",
IF(AND(D5<E5,E5F5),"C",
IF(AND(D5E5,E5<F5),"D","F"))))


--

HTH

Bob

"MelissaS" wrote in message
...
Can anyone tell me why this IF formula isn't working?

=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!

You cannot chain equations together. For example, D5<E5<F5 should be
AND(D5<E5,E5<F5). This error occurs 4 times in your equation.

Tom

"MelissaS" wrote:

Can anyone tell me why this IF formula isn't working?

=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!

"MelissaS" wrote:
Can anyone tell me why this IF formula isn't working?
=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

=IF(AND(D5<E5,E5<F5),"A",
IF(AND(D5E5,E5F5),"B",
IF(AND(E5D5,E5F5),"C",
IF(AND(E5<D5,E5<F5),"D","F"))))

Note that that returns "F" when E5=D5 or E5=F5. So if D5<E5=F5, it returns
"F" instead of "A". Is that what you really want?

Also, when you write D5<E5F5, do you intend to imply D5F5? As I wrote it,
the formula returns "C" for both D5<F5<E5 and F5<D5<E5. Was that your intent?

A similar arises with D5E5<F5.

If you resolve these issues differently, it is very likely that your formula
can be simplified.


----- original message -----

"MelissaS" wrote:

Can anyone tell me why this IF formula isn't working?

=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!

That is a good point about the relationship between D5 and F5. I don't
actually care whether D5 or < F5, I only care about the relationships
between D5 and E5 and then E5 and F5, so it looks like your original
suggestion will work. But your comment about my intent helped me think
through it a little bit more.
Thanks!

"Joe User" wrote:

"MelissaS" wrote:
Can anyone tell me why this IF formula isn't working?
=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

=IF(AND(D5<E5,E5<F5),"A",
IF(AND(D5E5,E5F5),"B",
IF(AND(E5D5,E5F5),"C",
IF(AND(E5<D5,E5<F5),"D","F"))))

Note that that returns "F" when E5=D5 or E5=F5. So if D5<E5=F5, it returns
"F" instead of "A". Is that what you really want?

Also, when you write D5<E5F5, do you intend to imply D5F5? As I wrote it,
the formula returns "C" for both D5<F5<E5 and F5<D5<E5. Was that your intent?

A similar arises with D5E5<F5.

If you resolve these issues differently, it is very likely that your formula
can be simplified.


----- original message -----

"MelissaS" wrote:

Can anyone tell me why this IF formula isn't working?

=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!

On Wed, 31 Mar 2010 12:10:07 -0700, MelissaS
wrote:

Can anyone tell me why this IF formula isn't working?

=IF(D5<E5<F5,"A",IF(D5E5F5,"B",
IF(D5<E5F5,"C",IF(D5E5<F5,"D","F"))))

It's returning "B" every time. D5, E5, and F5 are all derived from
formulas. Is that the problem? I appreciate any help!


Your syntax is the same as:
(D5<E5)<F5

So, depending on the values in D5 and E5, that first part will evaluate to TRUE
or FALSE. So, unless F5=TRUE, the construct will always evaluate to FALSE.
(Excel's default ordering has TRUE and FALSE being "greater than" most anything
else).

Conversely, your second IF will always evaluate to TRUE, unless F5=TRUE.

You've provided no particular context or examples or information as to the
contents of D5, E5 or F5 for your issue, but most who make this mistake find
that they are best served using the AND function.

For example: =and(d5<e5,e5<f5)







--ron