Hi There,
I have a long formula which is getting not only cumbersome but also complex to understand.
The formula is as below:
=IF(LEFT(T1,FIND("/",T1,1)-1)-RIGHT(T1,LEN(T1)-FIND("/",T1,1)) > RIGHT(T1,LEN(T1)-FIND("/",T1,1))*0.1,"Above 10%",
IF(LEFT(T1,FIND("/",T1,1)-1)-RIGHT(T1,LEN(T1)-FIND("/",T1,1)) > RIGHT(T1,LEN(T1)-FIND("/",T1,1))*0.03,"3% to 10%",
IF(LEFT(T1,FIND("/",T1,1)-1)-RIGHT(T1,LEN(T1)-FIND("/",T1,1)) > RIGHT(T1,LEN(T1)-FIND("/",T1,1))*0.03*-1,"-3% to 3%",
IF(LEFT(T1,FIND("/",T1,1)-1)-RIGHT(T1,LEN(T1)-FIND("/",T1,1)) > RIGHT(T1,LEN(T1)-FIND("/",T1,1))*0.1*-1,"-3% to -10%",
"Below -10%"))))
The value in cell T1 is in the format "10/10" the possible range is "-500" to "+500".
The function, as such is working fine for normal values.
Question 1: Is there an alternate way to achieve the same? More neat & clean. More readable. Better performing maybe. I understand IFS would have helped if it was an equal-to comparison, but not in this case. Or maybe I don't know how to do it using IFS.
Question 2: I also have one issue with this. When both the numerator and denominator are negative, the output is wrong. Could someone help me correct this.
Any help is highly appreciated and welcome.
Bookmarks