IF formula not working
in [Functions]
|
From: MelissaS on 31 Mar 2010 15:10 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!
From: Glenn on 31 Mar 2010 15:26 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! > > You can't use multiple comparisons that way. Try it like this: =IF(AND(D5<E5,E5<F5),"A",IF(AND(...
From: Bob Phillips on 31 Mar 2010 15:25 TRy =IF(AND(D5<E5,E5<F5),"A", IF(AND(D5>E5,E5>F5),"B", IF(AND(D5<E5,E5>F5),"C", IF(AND(D5>E5,E5<F5),"D","F")))) -- HTH Bob "MelissaS" <MelissaS(a)discussions.microsoft.com> wrote in message news:D8FB3409-635F-40C9-A745-004450258182(a)microsoft.com... > 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! > >
From: tompl on 31 Mar 2010 15:31 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(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! > >
From: Joe User on 31 Mar 2010 15:44
"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! > > |