Alternative to long nested IF formula

mrwad

New Member
Joined
Oct 16, 2018
Messages
47
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?

Code:
=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

bTmi0.png
 
Last edited:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,086
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
Apr 13, 2012
Messages
338
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

New Member
Joined
Oct 16, 2018
Messages
47
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top