+ Reply to Thread
Results 1 to 6 of 6

Help needed top break the complex nested if formula

  1. #1
    Registered User
    Join Date
    06-25-2012
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Help needed top break the complex nested if formula

    Hi,

    Please can anyone help in splitting or put this nested if formula in englisg so that I can undestand the logic behind. The help will be very much appreciated. thanks in adavnec.

    below is the function:


    =IF(IFERROR(IF(IF((ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0))<(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")),(ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0)),(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")))=0,(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")),(IF((ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0))<(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")),(ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0)),(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")))))," ") < 0,"0",IFERROR(IF(IF((ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0))<(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")),(ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0)),(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")))=0,(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")),(IF((ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0))<(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")),(ROUND(IF(T2=0,0,((((T2-TODAY())/7)*K2))-(L2+M2)),0)),(IFERROR((ROUND(((IF(ISERROR(IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)),"",IF(((F2+L2+M2)/K2)>15,(F2-((((F2+L2+M2)/K2)-15)*K2)),F2)))/(H2*I2)),0))*(H2*I2)," ")))))," "))

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help needed top break the complex nested if formula

    hmm i stared at it for a long time and i think i see a picture of a cat..no idea other than that. perhaps if a sample workbook was given?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-25-2012
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Help needed top break the complex nested if formula

    hi martind wilson,

    I dont have a sample spreadsheet. I would be glad if you can explain me how and whre to split this nested if into simple if statements. I am very novice with excel applications, tat too especially with this kind pf formulae though i know the basics. May be please expalin one nested if then i can take from there.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,238

    Re: Help needed top break the complex nested if formula

    martin, thats not a cat, its a doggie with a winter hoodie on
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help needed top break the complex nested if formula

    I think the reason for these replies is because that formula is so massive, without any background on what it actually does it's hard to decipher it.

    But if you just want to break it down into smaller pieces, it's pretty easy.
    Just look for pieces that are repeated over and over in the formula.
    For example, F2+L2+M2

    You can put in a cell, say A1 =F2+L2+M2
    Then within that formula replace all instances of F2+L2+M2 with A1

    Then keep repeating that process for strings that are repetitive in the formula.
    Like H2*I2 and T2-TODAY()

    Eventually you'll get it broken down into much smaller pieces and it may be easier to read and understand what it's doing.


    And, how can you NOT have a sample sheet? If you don't have a sheet, where did the formula come from??

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,572

    Re: Help needed top break the complex nested if formula

    A little work in Word and it looks better. Still it should be devided into helper columns.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Buy the way, you can make it look this nice in the formula bar as well by using Alt + Enter and then stretch the formula bar to proper size.
    Last edited by Jacc; 05-07-2013 at 02:54 PM.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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