IF formula (cumulative)

overbomb

New Member
Joined
Feb 28, 2013
Messages
10
Hi

I am failing to write a IF formula which will inform of impact of a bottleneck / constraint - some help would be greatly appreciated

Row 7 - Before bottleneck
This represents the amount of items I want to process

Row 6 - Bottleneck
This represent the amount of items that can be processed (constraint)

Row 5 - my formula
This is the formula I have written to reflect what can be made within the constraint but it is not working

Row 4 - after bottleneck (impact of my current formula
This is the answer being produced by my formula

Row 3 - what the answer 'should be'
This is what the answer should be

Example - cumulative
E7 the number of items I want to make, 17 (Before bottleneck)

Up to this point, B7 - D7, I have wanted to process 133 (69+28+36), but the constraint, B6 - D6 is 120 (40+40+40)
Therefore, I am carrying forward 13 from SEP (row 2 header) into OCT, meaning that I now need to process 17 (OCT) + 13 (carry fwd) = 30.
Meaning my answer in E4 should be 30 (as per 'should be' cell E3).
However, my formula is showing 17

Which means that moving into NOV, I have no carry over, so I can process all 31, same as DEC, I can process all 25.

I cannot figure out how to amend my formula (I have tried various mutations) to accommodate cumulative effect.

Can someone please help me

Many thanks, Maddy
Capture - IF formula.PNG
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hello there

You formulas don't make sense to me.
The first one I expected to reference values in rows 6 and 7 but instead it refers to rows 4 and 5 thus creating a circular reference.
The second formula refers to two cells in column I but both are empty. How can the formula show 206 as a result?

I suggest cleaning up both formulas and try again,
 
Upvote 0
I came up with this, but my Oct number is off by 1 for some reason: (I think your 31 is a typo).

Book1
ABCDEFG
1
2
3
4
52024-07-012024-08-012024-09-012024-10-012024-11-012024-12-01
6Bottleneck404040404040
7Before Bottleneck692836172135
8Numbr to process404040302135
Sheet1
Cell Formulas
RangeFormula
C5:G5C5=EDATE(B5,1)
B8:G8B8=IF(SUM($B7:B7)>=SUM($B6:B6),MAX(SUM($B7:B7)-SUM($B6:B6),B6), IF(A8<A6,B7, B6-(SUM($B6:B6)-SUM($B7:B7))))
 
Upvote 0
I came up with this, but my Oct number is off by 1 for some reason: (I think your 31 is a typo).

Book1
ABCDEFG
1
2
3
4
52024-07-012024-08-012024-09-012024-10-012024-11-012024-12-01
6Bottleneck404040404040
7Before Bottleneck692836172135
8Numbr to process404040302135
Sheet1
Cell Formulas
RangeFormula
C5:G5C5=EDATE(B5,1)
B8:G8B8=IF(SUM($B7:B7)>=SUM($B6:B6),MAX(SUM($B7:B7)-SUM($B6:B6),B6), IF(A8<A6,B7, B6-(SUM($B6:B6)-SUM($B7:B7))))
thank you - this has worked perfectly
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

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
Back
Top