Complex Nested IF issue

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.

An attachment with some possible values for all your scenarios and desired outputs would be helpful to reach the solution. Follow the yellow banner

the below may be of interest, if the formula only applies to a few cells
https://exceloffthegrid.com/turn-str...-with-evalute/

Thanks everyone for taking some time for this.

probably i am not clear in my requirement.

The below code is working perfectly fine now. Though it is not a smart way to achieve this.
i have taken a smart code and converted it back to a dumb code.

=IF(VALUE(REPLACE(T1,1,FIND("/",T1),"")) < 0,
LOOKUP(ROUND(1-((LEFT(T1,FIND("/",T1)-1)/REPLACE(T1,1,FIND("/",T1),""))),3),{-9.99E+307,-0.1,-0.03,0.031,0.101},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"}),
LOOKUP(ROUND((LEFT(T1,FIND("/",T1)-1)/REPLACE(T1,1,FIND("/",T1),""))-1,3),{-9.99E+307,-0.1,-0.03,0.031,0.101},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})
)


The requirement is to calculate what % is the delta of the two values.
so if my number is a/b the output % is ((a-b)/b)*100

cell value..............delta%..........output
85/100.................-15%............Below -10%
-85/100...............-185%...........Below -10%
85/-100...............185%............Above 10%
-85/-100..............185%............Above 10%

using the formula
LOOKUP(ROUND((LEFT(T1,FIND("/",T1)-1)/REPLACE(T1,1,FIND("/",T1),""))-1,3),{-9.99E+307,-0.1,-0.03,0.031,0.101},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})

gives the output wrong for the values where the denominator is negative. so adding an if condition to reverse the formula where denominator is negative solves the issue and i am now getting the correct output.

though the formula now again looks repetitive and cumbersome. like my original IF formula was looking like.

Originally Posted by AliGW

Please update your profile to Office 365 (days in the year). 360 is incorrect - that's degrees in a circle.

that was a typo, corrected it. Thanks

I agree with Ali
cell value..............delta%..........output
85/100.................-15%............Below -10%
-85/100...............-185%...........Below -10%
85/-100...............185%............Above 10%
-85/-100..............185%............Above 10%


is this true? all the above are below -10% by my logic

uploaded sample file

for 85/-100

a= 85
b=-100

delta = a-b = 185 => on a graph the difference between 85 and -100 is 185
delta % = ((a-b)/b)*100 = 185%

Hi Ali,

I have already mentioned that with the changes in the code, i now do have the correct formula for all scenario.
But i feel it is not a smart way to do it. and looking for guidance in achieving the same with better performance.

The very first code i wrote used about 67 function calls to achieve this result. a sure shot bad code.
It is now down to 17 function calls. way better than before.

can we still improve it? can we make it even better?

Thanks again for all your time and guidance.

It appears to work Ali, my question would be is the bottom number ever not 100 if it changes the formula needs amending to divide by this.

if not
=LOOKUP((LEFT(B3,FIND("/",B3)-1)-MID(B3,FIND("/",B3)+1,4))/MID(B3,FIND("/",B3)+1,4)),{-9.99E+307,-0.1,-0.3,0.30000001,1.00000000.1},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})

we will await Manu's response

Hi Ali,

I used the denominator as 100 for ease of calculation when verifying the output manually.
in real data this may or may not be 100.

below are some of the real values:
-137/-156, 37/32, -57/-59, 9.5/6.5, 81/71, -20/-13, -42/-40, -13/-18, -17/-28, -12/-13

against these values the formula will not yield correct result as you are only using the difference between numerator & denominator. Not the percentage.

I am uploading the sample again with these values added in.

Originally Posted by davsth

It appears to work Ali, my question would be is the bottom number ever not 100 if it changes the formula needs amending to divide by this.

if not
=LOOKUP((LEFT(B3,FIND("/",B3)-1)-MID(B3,FIND("/",B3)+1,4))/MID(B3,FIND("/",B3)+1,4)),{-9.99E+307,-0.1,-0.3,0.30000001,1.00000000.1},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})

we will await Manu's response

using the format a/b
for number whose denominator is non-negative (a-b)/b*100 will give correct percentage.

as a work around for the numbers where denominator is negative, i am using formula (b-a)/b*100 and it is giving the correct result.

which i have used in a very crude manner in my code saying: IF (denominator < 0, (b-a)/b*100, (a-b)/b*100)
also i am convinced this is a bad way of achieving this.

also the possible range is indeed between -500 to 500 for both numerator or denominator.

big thanks

i have never made any changes to what my requirement is.
i have since start given the mathematical equivalent of what i am trying for. no change in that ever.
the data is only representative. it doesn't change the requirement in any way.

thanks for your time anyway.

ok understood. will be more careful about that in future.

Hi Samba

Indeed, this solves the denominators sign problem completely. This is perfect. Thank you so much.
You have been a great help.

I have shorten it a bit further to:
=LOOKUP(((LEFT(B3,FIND("/",B3)-1)/ABS(MID(B3,FIND("/",B3)+1,4)))-1)*100,{-9.99E+307,-10,-3,3.1,10.1},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})

This is perfect solution. From 67 function calls it has come down to 6 function calls.
very neat and crispy. Thank you!

I have one curiosity, i see many people preferred to use MID instead of RIGHT. Do you have any particular reason for that? or is it just practice?

right, so that's another function call. got it. Thanks. You are awesome!!

how do i mark this thread as closed/solved ?

In the FAQs it says

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved