+ Reply to Thread
Results 1 to 33 of 33

Complex Nested IF issue

  1. #1
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,765

    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%"})
    Last edited by XLent; 05-28-2020 at 11:03 AM.

  3. #3
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Complex Nested IF issue

    Hi XLent.

    what i had managed till now was this
    =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%"})
    )
    Last edited by Manu Sunil; 05-29-2020 at 04:36 AM.

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,002

    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

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,426

    Re: Complex Nested IF issue

    How about:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,002

    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/

  7. #7
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  9. #9
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Complex Nested IF issue

    Quote Originally Posted by AliGW View Post
    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

  10. #10
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,002

    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

  11. #11
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Complex Nested IF issue

    uploaded sample file
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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%

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    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?

  14. #14
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    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

  16. #16
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,553

    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
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    Re: Complex Nested IF issue

    SNAP. Samba!

  18. #18
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,002

    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

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    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".

  20. #20
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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.

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

  21. #21
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Complex Nested IF issue

    Quote Originally Posted by davsth View Post
    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

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    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.

  23. #23
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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.

    thanks for your time anyway.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    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.

  25. #25
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Complex Nested IF issue

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

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,141

    Re: Complex Nested IF issue

    Thank you.

  27. #27
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,553

    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

  28. #28
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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!

  29. #29
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    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?

  30. #30
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,553

    Re: Complex Nested IF issue

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

  31. #31
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Complex Nested IF issue

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

  32. #32
    Registered User
    Join Date
    05-27-2020
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Complex Nested IF issue

    how do i mark this thread as closed/solved ?

  33. #33
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,002

    Re: Complex Nested IF issue

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Nested CONTAINS within IF(COUNTIF()) for complex database
    By Cont-Kevin in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-15-2017, 11:34 AM
  2. Complex Nested Condition
    By venkatraman_r in forum Excel General
    Replies: 5
    Last Post: 09-20-2016, 07:11 AM
  3. Complex nested loop problem
    By jrx in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-27-2016, 09:59 AM
  4. Complex nested ifs
    By ss1991 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-26-2016, 01:08 PM
  5. Nested If Complex Formula
    By fairchance in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-25-2015, 09:20 AM
  6. complex nested IF statement
    By snoopy990 in forum Excel General
    Replies: 2
    Last Post: 07-01-2011, 01:28 AM
  7. Nested IF Statement-complex
    By Kheldar in forum Excel General
    Replies: 11
    Last Post: 02-09-2011, 10:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1