I'm writing IFS Formula in Excel... I have positive and negative numbers that I'm grouping into categories. My formula works for positive numbers, but when it reads negative numbers, it results in a "0" answer. Question is - how can I get it to read negative numbers? In the example below, you see that positive numbers are categorized correctly, but negative numbers default to "0-80". Thank you!
Ex. Current Formula:
=IFS(EN:EN<-320,"-320+",EN:EN<-320,"-320--241",EN:EN<-240,"-160--240",EN:EN<-160,"-160--81",EN:EN<80,"0-80",EN:EN<160,"81-160",EN:EN<240,"161-240",EN:EN<320,"241-320",EN:EN<400,"321-400",EN:EN<480,"401-480",EN:EN<560,"481-560",EN:EN<640,"561-640",EN:EN>640,"640+")
*EN:EN is the column on my Excel sheet.
Output from Formula on my Excel sheet:
31.38 0-80
17.20 0-80
54.69 0-80
306.00 241-320
76.20 0-80
87.91 81-160
(112.00) 0-80
EN:EN<-320,"-320+",EN:EN<-320,"-320--241"
^^^^^^^^^^ ^^^^^^^^^^
the second will never be found as the IFS stops at the first true.
You do not account for -80 to 0
Your conditions are off for the negative numbers:
=IFS(EN:EN<-320,"-320+",EN:EN<-240,"-320--241",EN:EN<-160,"-240--160",EN:EN<-80,"-160--81",EN:EN<0,"-80-0",EN:EN<80,"0-80",EN:EN<160,"81-160",EN:EN<240,"161-240",EN:EN<320,"241-320",EN:EN<400,"321-400",EN:EN<480,"401-480",EN:EN<560,"481-560",EN:EN<640,"561-640",EN:EN>640,"640+")