Ifs negative values error

%3CLINGO-SUB%20id%3D%22lingo-sub-2066337%22%20slang%3D%22en-US%22%3EIfs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066337%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20doing%20great%2C%26nbsp%3B%20kindly%20help%20me%20for%20the%20below%20error%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20want%20check%203%20condition%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(M1%26gt%3B%3D0.5%2C%20%22GREEN%22%2CM1%26lt%3B%3D0.5%2C%22%20AMBER%22%2CM1%26lt%3B%3D-0.1%2C%22RED%22)%3C%2FP%3E%3CP%3Elike%20A%26gt%3B%3D0.5%2C%20GREEN%2C%3C%2FP%3E%3CP%3EA%26lt%3B%3D0.5%2C%20AMBER%2C%3C%2FP%3E%3CP%3EA%26lt%3B%3D-0.1%20RED..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGREEN%20%26amp%3B%20AMBER%20Condition%20working%20perfect%20but%20-%20values%20not%20calculating%20so%20please%20help%20me%20soon%20as..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2066337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066351%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931234%22%20target%3D%22_blank%22%3E%40SUDHAKAR-FRONTIER%3C%2FA%3E%26nbsp%3BTry%20it%20this%20way%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(M1%26gt%3B%3D0.5%2C%20%22GREEN%22%2CM1%26lt%3B%3D-0.1%2C%22RED%22%2CM1%26lt%3B%3D0.5%2C%22%20AMBER%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYour%20original%20formula%20never%20came%20past%20the%20AMBER%20test%20as%20a%20number%20%26lt%3B%3D%20-0.1%20is%20also%20%26lt%3B%3D%200.5%20.%20So%2C%20you%20need%20to%20put%20the%20RED%20test%20before%20the%20AMBER%20test.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066676%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERiny%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStill%20same%20error%20coming%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elike%3C%2FP%3E%3CP%3E3%20conditions.%3C%2FP%3E%3CP%3E1.%20m1%26lt%3B%3D0.5%2C%20%22green%22.%3C%2FP%3E%3CP%3E2.%20m1%26gt%3B%3D0.5%2C%20%22amber%22.%3C%2FP%3E%3CP%3E3.%20m1%26gt%3B%3D0%2C%20%22red%22%20--%26gt%3B%20so%20here%200%20and%20minus%20values%20should%20reflecting%20in%20red%20but%20above%20function%20only%20-%20values%20taking%2C%20Its%20not%20taking%200%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20help%20me%20on%203rd%20condition%2C%20its%20should%20take%200%20and%20minus%20values%20also%20for%20red..%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066680%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931234%22%20target%3D%22_blank%22%3E%40SUDHAKAR-FRONTIER%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20yo%20mean%20M1%26lt%3B%3D0%20as%20condition%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066684%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931234%22%20target%3D%22_blank%22%3E%40SUDHAKAR-FRONTIER%3C%2FA%3E%26nbsp%3BAs%20suggested%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20try%20this%20then%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(M1%26gt%3B%3D0.5%2C%22GREEN%22%2CM1%26lt%3B%3D0%2C%22RED%22%2CM1%26lt%3B%3D0.5%2C%22AMBER%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066696%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22IFS.PNG%22%20style%3D%22width%3A%20632px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246872i5BE12253F7AF9C0D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22IFS.PNG%22%20alt%3D%22IFS.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStill%20i%20Am%20getting%20same%20error%2C%20Please%20find%20the%20screen%20short..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066707%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931234%22%20target%3D%22_blank%22%3E%40SUDHAKAR-FRONTIER%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBetter%20to%20look%20at%20a%20working%20example.%20See%20attached%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066718%22%20slang%3D%22en-US%22%3ERe%3A%20Ifs%20negative%20values%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20rounding%20issue%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(n%2C%20ROUND(M2%2C0)%2C%20IFS(n%26gt%3B%3D0.5%2C%22GREEN%22%2Cn%26lt%3B%3D0%2C%22RED%22%2Cn%26lt%3B%3D0.5%2C%22AMBER%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Dear All,

 

I hope doing great,  kindly help me for the below error,

 

i want check 3 condition,

 

=IFS(M1>=0.5, "GREEN",M1<=0.5," AMBER",M1<=-0.1,"RED")

like A>=0.5, GREEN,

A<=0.5, AMBER,

A<=-0.1 RED..

 

GREEN & AMBER Condition working perfect but - values not calculating so please help me soon as..

7 Replies

@SUDHAKAR-FRONTIER Try it this way:

=IFS(M1>=0.5, "GREEN",M1<=-0.1,"RED",M1<=0.5," AMBER")

Your original formula never came past the AMBER test as a number <= -0.1 is also <= 0.5 . So, you need to put the RED test before the AMBER test.

@Riny_van_Eekelen

 

Riny

 

Still same error coming,

 

like

3 conditions.

1. m1<=0.5, "green".

2. m1>=0.5, "amber".

3. m1>=0, "red" --> so here 0 and minus values should reflecting in red but above function only - values taking, Its not taking 0,

 

So help me on 3rd condition, its should take 0 and minus values also for red.. 

@SUDHAKAR-FRONTIER 

Perhaps yo mean M1<=0 as condition

@SUDHAKAR-FRONTIER As suggested by @Sergei Baklan , try this then:

=IFS(M1>=0.5,"GREEN",M1<=0,"RED",M1<=0.5,"AMBER")

 

@Riny_van_Eekelen IFS.PNG

 

Still i Am getting same error, Please find the screen short..

 

@SUDHAKAR-FRONTIER 

Better to look at a working example. See attached workbook.

 

@Riny_van_Eekelen 

Perhaps rounding issue

=LET(n, ROUND(M2,0), IFS(n>=0.5,"GREEN",n<=0,"RED",n<=0.5,"AMBER"))