IF formula not working
in [Functions]
|
From: Ron Rosenfeld on 31 Mar 2010 15:47 On Wed, 31 Mar 2010 12:10:07 -0700, MelissaS <MelissaS(a)discussions.microsoft.com> wrote: >Can anyone tell me why this IF formula isn't working? > >=IF(D5<E5<F5,"A",IF(D5>E5>F5,"B", >IF(D5<E5>F5,"C",IF(D5>E5<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
From: MelissaS on 31 Mar 2010 16:30
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(D5>E5>F5,"B", > > IF(D5<E5>F5,"C",IF(D5>E5<F5,"D","F")))) > > =IF(AND(D5<E5,E5<F5),"A", > IF(AND(D5>E5,E5>F5),"B", > IF(AND(E5>D5,E5>F5),"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<E5>F5, do you intend to imply D5>F5? 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 D5>E5<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(D5>E5>F5,"B", > > IF(D5<E5>F5,"C",IF(D5>E5<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! > > > > |