Alternative to long nested IF formula

[mrwad]

I have developed nested IF formula that works fine but I have to use it in 44835 cells that makes my Excel file 4,8 Mb in size and worksheet pretty slow on i5 Intel with 8 Gb of ram. Is there any way to rewrite formula to make it more efficient or maybe do something else to speed up my program?

=IF(P11="";"";IF(OR(P$9="MO";P$9="TU";P$9="WE";P$9="TH";P$9="FR");IF(P11<=8;P11*$K$37*($C11+$D11+$E11);8*($K$37)*$C11+IF(MAX(0;P11-8)-MAX(0;P11-10)<0;0;((MAX(0;P11-8))-(MAX(0;P11-10)))*$K$15*($C11+$D11+$E11)*(1+Data!$U$2))+IF((MAX(0;P11-8)-2)<=0;0;(MAX(0;P11-8)-2)*$K$37*($C11+$D11+$E11)*(1+Data!$V$2)));IF(P$9="SA";IF(P11<=8;P11*$K$37*($C11+$D11+$E11)*(1+Data!$T$3);8*$K$37*$C11*(1+Data!$T$3)+IF((P11-8)<0;0;(P11-8)*$K$37*$C11*(1+Data!$U$3)));IF(P$9="SU";IF(COUNTA($E11:$O11)=6;P11*($K$15*$C11*(1+Data!$W$3));P11*($K$37*$C11*(1+Data!$V$3)));""))))

Task:

If it is MO, TU, WE, TH, FR and Working time is <= 8 -> perform calculation -> if Working time is > 8 perform another calculation
If it is SA and Working time is <= 8 -> perform calculation -> if Working time is > 8 perform another calculation
If it is SU and COUNTA($E11:$O11)=6 -> perform calculation -> if COUNTA($E11:$O11)=6 = FALSE perform another calculation

 

[RoryA]

First, please remind yourself of the forum rule on cross-posting and comply with it.

Second, are you sure this formula is what is slowing down the workbook? IF functions are good at only evaluating the necessary parts, so are generally quite efficient. Also, 4.8MB is not big for a workbook.

 

[BlueHornet]

You're right, that formula is a horror show. Since I can't evaluate all of it, I can at least offer you some tips on how to modify it.

Days of the week are as regular as clockwork. Regardless of the calendar date, every seventh day is a Monday, Tuesday, Wednesday, etc. So there should be no reason to look back at some other day in the week to see what that day was. Since we can evaluate day by day what "today" is, then the rest of the week works out automatically. Also, since your formula changes for Saturdays and for Sundays, then those days can have their own dedicated formulas without the necessity to check "is it Saturday?" or "is it Sunday?"

So have one dedicated formula for Saturday, one dedicated formula for Sunday and another formula for the other five weekdays. You can copy this across the row in blocks of seven cells each (or into a range that is divisible by 7), and eliminate that needless complexity.

Since I also suspect that you're using this as part of a payroll calculator, it might be helpful (for you and for later reviewers or auditors) to break out the Straight Time, Time-and-a-half Overtime and Double-time Overtime onto separate lines (and using their own formulas, where they apply) to show that more clearly. Again, it means more formulas and more cells, but visibility will be vastly improved. (I can't say whether the file size will be any smaller, but I'd be willing to bet that computation time will be speeded up.)

Since you frequently refer to the term of $C11 + $D11 + $E11, I would also suggest that you include a helper column at $F11 to perform the sum at that point and then refer to that one cell in the formula. Readability will improve from that one step.

I'm also seeing terms (at least once) of "IF(( MAX( 0, P11 - 8) - 2) <= 0" ... aren't you just evaluating there whether P11 >= 10? Why all of the convolution? In any case, it makes no sense to evaluate whether the max of 0 - 2 is <= 0, because it always is, forever.

It seems to me that it's going to be important for you to evaluate the formula/s that you come up with term by term to examine why you need all of the terms, and why they need to be written 'just so'.

 

[mrwad]

You're right, that formula is a horror show. Since I can't evaluate all of it, I can at least offer you some tips on how to modify it.

Days of the week are as regular as clockwork. Regardless of the calendar date, every seventh day is a Monday, Tuesday, Wednesday, etc. So there should be no reason to look back at some other day in the week to see what that day was. Since we can evaluate day by day what "today" is, then the rest of the week works out automatically. Also, since your formula changes for Saturdays and for Sundays, then those days can have their own dedicated formulas without the necessity to check "is it Saturday?" or "is it Sunday?"

So have one dedicated formula for Saturday, one dedicated formula for Sunday and another formula for the other five weekdays. You can copy this across the row in blocks of seven cells each (or into a range that is divisible by 7), and eliminate that needless complexity.

Since I also suspect that you're using this as part of a payroll calculator, it might be helpful (for you and for later reviewers or auditors) to break out the Straight Time, Time-and-a-half Overtime and Double-time Overtime onto separate lines (and using their own formulas, where they apply) to show that more clearly. Again, it means more formulas and more cells, but visibility will be vastly improved. (I can't say whether the file size will be any smaller, but I'd be willing to bet that computation time will be speeded up.)

Since you frequently refer to the term of $C11 + $D11 + $E11, I would also suggest that you include a helper column at $F11 to perform the sum at that point and then refer to that one cell in the formula. Readability will improve from that one step.

I'm also seeing terms (at least once) of "IF(( MAX( 0, P11 - 8) - 2) <= 0" ... aren't you just evaluating there whether P11 >= 10? Why all of the convolution? In any case, it makes no sense to evaluate whether the max of 0 - 2 is <= 0, because it always is, forever.

It seems to me that it's going to be important for you to evaluate the formula/s that you come up with term by term to examine why you need all of the terms, and why they need to be written 'just so'.

Thank you for your very detailed reply! I will try to repair it. However if you are interested in this stuck project you can have it for review. Maybe it will be easier to understand the point in this calculation.

You can see GanttChart sheet with chart where you can input data, Calculation has all the massive IF formulas and Data sheet has overwork matrix with hours and percentages to be added after certain amount of hours each day. Cost is on Calculation sheet you can also see Total calculation on the most right (if you scroll) in Calculation sheet.

https://drive.google.com/file/d/17cKN01V9EMGXhQR2xMHOUM0p5VL-tSJh/view?usp=sharing