# Complex Nested IF issue

1. ## 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.  Register To Reply

2. ## Re: Complex Nested IF issue

not sure I totally follow but, perhaps, something along lines of:

=LOOKUP(ROUND((LEFT(T1,FIND("/",T1)-1)-REPLACE(T1,1,FIND("/",T1),""))/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%"})

edit - or:

=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%"})  Register To Reply

3. ## Re: Complex Nested IF issue

Hi XLent.

=IF((LEFT(T1,FIND("/",T1,1)-1)/RIGHT(T1,LEN(T1)-FIND("/",T1,1)))-1 > 0.1,"Above 10%",
IF((LEFT(T1,FIND("/",T1,1)-1)/RIGHT(T1,LEN(T1)-FIND("/",T1,1)))-1 > 0.03,"3% to 10%",
IF((LEFT(T1,FIND("/",T1,1)-1)/RIGHT(T1,LEN(T1)-FIND("/",T1,1)))-1 > 0.03*-1,"3% to -3%",
IF((LEFT(T1,FIND("/",T1,1)-1)/RIGHT(T1,LEN(T1)-FIND("/",T1,1)))-1 > 0.1*-1,"-3% to -10%", "Below -10%"))))

Your code is definitely more readable and way smarter than the dumb code i had written. Thank you very much.
This has solved my first problem. The second issue still remains though. Which to be honest is more of a mathematics issue.

whenever is the denominator a negative number, the values are not right.
let's take an example of T1 as -85/-100. Now this value should be marked as Above 10% but the code will mark it as Below -10%.
This would be the same for 85/-100.

If i change the code to below then it works fine. But this is again repetitive and not smart.

=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%"})
)  Register To Reply

4. ## Re: Complex Nested IF issue

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

5. ## Re: Complex Nested IF issue ``Please Login or Register  to view this content.``  Register To Reply

6. ## Re: Complex Nested IF issue

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

7. ## Re: Complex Nested IF issue

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.  Register To Reply

8. ## Re: Complex Nested IF issue

There are instructions at the top of the page explaining how to attach your sample workbook.

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

9. ## Re: Complex Nested IF issue 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  Register To Reply

10. ## Re: Complex Nested IF issue

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  Register To Reply

11. ## Re: Complex Nested IF issue  Register To Reply

12. ## Re: Complex Nested IF issue

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%  Register To Reply

13. ## Re: Complex Nested IF issue

So what's the problem with the formula in column E? Why can't you use that, since it produces the right answers?   Register To Reply

14. ## Re: Complex Nested IF issue

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.  Register To Reply

15. ## Re: Complex Nested IF issue

Try this:

=LOOKUP(LEFT(B3,FIND("/",B3)-1)-MID(B3,FIND("/",B3)+1,4),{-9.99E+307,-10,-3,3.1,10.1},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})

Excel 2016 (Windows) 32 bit
B
C
D
E
F
G
1
this is wrong
this is right
2
Values
Delta %
Formula 1
Formula 2
Expected Output
AliGW
3
85/100
-15
Below -10%
Below -10%
Below -10%
Below -10%
4
-85/100
-185
Below -10%
Below -10%
Below -10%
Below -10%
5
85/-100
185
Below -10%
Above 10%
Above 10%
Above 10%
6
-85/-100
15
Below -10%
Above 10%
Above 10%
Above 10%
7
98/100
-2
-3% to 3%
-3% to 3%
-3% to 3%
-3% to 3%
8
-98/100
-198
Below -10%
Below -10%
Below -10%
Below -10%
9
98/-100
198
Below -10%
Above 10%
Above 10%
Above 10%
10
-98/-100
2
-3% to 3%
-3% to 3%
-3% to 3%
-3% to 3%
11
102/100
2
-3% to 3%
-3% to 3%
-3% to 3%
-3% to 3%
12
-102/100
-202
Below -10%
Below -10%
Below -10%
Below -10%
13
102/-100
202
Below -10%
Above 10%
Above 10%
Above 10%
14
-102/-100
-2
-3% to 3%
-3% to 3%
-3% to 3%
-3% to 3%
15
94/100
-6
-3% to -10%
-3% to -10%
-3% to -10%
-3% to -10%
16
-94/100
-194
Below -10%
Below -10%
Below -10%
Below -10%
17
94/-100
194
Below -10%
Above 10%
Above 10%
Above 10%
18
-94/-100
6
-3% to -10%
3% to 10%
3% to 10%
3% to 10%
 Sheet: Sheet1  Register To Reply

16. ## Re: Complex Nested IF issue

F3=LOOKUP(LEFT(B3,FIND("/",B3)-1)-MID(B3,FIND("/",B3)+1,6),{-9.99E+307,-10,-3,3.01,10.01},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})
Try above formula, Copy and paste towards down  Register To Reply

17. ## Re: Complex Nested IF issue

SNAP. Samba!   Register To Reply

18. ## Re: Complex Nested IF issue

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  Register To Reply

19. ## Re: Complex Nested IF issue

Yep - I did wonder about that myself, but one can only work with what one is given. He did say this, however:

The value in cell T1 is in the format "10/10" the possible range is "-500" to "+500".  Register To Reply

20. ## Re: Complex Nested IF issue

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.  Register To Reply

21. ## Re: Complex Nested IF issue 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   Register To Reply

22. ## Re: Complex Nested IF issue

OK - I'm out. Sorry, but I don't play the game of shifting goalposts.

Members invest significant time trying to help you - they can only work with what you give them, so providing data that doesn't tell the whole picture leads to their wasting their time.  Register To Reply

23. ## Re: Complex Nested IF issue

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.  Register To Reply

24. ## Re: Complex Nested IF issue

You don't understand. Members will try to solve your issues based on the data you give them. You need to make sure that the sample data covers all scenarios, because they may (and can, if thy wish) use ANY of the data you've provided in their solution.  Register To Reply

25. ## Re: Complex Nested IF issue

ok understood. will be more careful about that in future.  Register To Reply

26. ## Re: Complex Nested IF issue

Thank you.  Register To Reply

27. ## Re: Complex Nested IF issue

=LOOKUP((LEFT(B3,FIND("/",B3)-1)-MID(B3,FIND("/",B3)+1,4))/ABS(MID(B3,FIND("/",B3)+1,4))*100,{-9.99E+307,-10,-3,3.1,10.1},{"Below -10%","-3% to -10%","-3% to 3%","3% to 10%","Above 10%"})
try this, copy and paste towards down  Register To Reply

28. ## Re: Complex Nested IF issue

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!  Register To Reply

29. ## Re: Complex Nested IF issue

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?  Register To Reply

30. ## Re: Complex Nested IF issue

I think, If you use Right Function, you need to use one additional function len along with find function  Register To Reply

31. ## Re: Complex Nested IF issue

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

32. ## Re: Complex Nested IF issue

how do i mark this thread as closed/solved ?  Register To Reply

33. ## Re: Complex Nested IF issue

In the FAQs it says

New Method
-Go to the top of the first post  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 