Help needed top break the complex nested if formula

1. 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. 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?

3. 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. Re: Help needed top break the complex nested if formula

martin, thats not a cat, its a doggie with a winter hoodie on

5. 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. 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:
`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.

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

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