Ifs negative values error

Brass 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"))