SOLVED

IF formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1623569%22%20slang%3D%22en-US%22%3EIF%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623569%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20entered%20this%20formula%20to%20returneither%20a%20%221%22%2C%20%222%22%20or%20%223%22%20depending%20on%20the%20value%20stated%2C%20yet%20it%20only%20returns%20%221%22...%20wondering%20what%20I%20have%20done%20wrong%20please%3F%20%3DIF(G3%26gt%3B4999%2C1%2C0*(IF(G3%26gt%3B19999%2C2%2C0*(IF(G3%26gt%3B29999%2C3%2C0)))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1623569%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1623598%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F777207%22%20target%3D%22_blank%22%3E%40ando67%3C%2FA%3E%26nbsp%3B%20Based%20on%20your%20formula%20you%20should%20get%20either%201%20or%200%20because%20you%20multiply%20the%20false%20case%20by%200.%26nbsp%3B%20Try%20this%20instead%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(G3%26gt%3B29999%2C3%2CIF(G3%26gt%3B19999%2C2%2CIF(G3%26gt%3B4999%2C1%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%20more%20readable%20version%20would%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(G3%26gt%3B29999%2C3%2CG3%26gt%3B19999%2C2%2CG3%26gt%3B4999%2C1%2CTRUE%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1623649%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623649%22%20slang%3D%22en-US%22%3E%3CP%3EAh%20thanks!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%20That%20jogged%20the%20ol'%20memory%20-%20Cheers'n'Beers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I have entered this formula to returneither a "1", "2" or "3" depending on the value stated, yet it only returns "1"... wondering what I have done wrong please? =IF(G3>4999,1,0*(IF(G3>19999,2,0*(IF(G3>29999,3,0)))))

2 Replies
Best Response confirmed by ando67 (New Contributor)
Solution

@ando67  Based on your formula you should get either 1 or 0 because you multiply the false case by 0.  Try this instead:

=IF(G3>29999,3,IF(G3>19999,2,IF(G3>4999,1,0)))

or more readable version would be:

=IFS(G3>29999,3,G3>19999,2,G3>4999,1,TRUE,0)

Ah thanks!@mtarler That jogged the ol' memory - Cheers'n'Beers