IF formula (cumulative)

[overbomb]

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

 

[shift-del]

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,

 

[Snakehips]

@overbomb Does this help?

AutoTraining.xlsm
	A	B	C	D	E	F	G	H
1
2		Jul	Aug	Sep	Oct	Nov	Dec	FY
3	Achievable	40	40	40	30	31	25	206
4
5	Bottleneck	40	40	40	40	40	40	240
6	Target	69	28	36	17	31	25	206
7
8
9
10	Cumulative Target	69	97	133	150	181	206
11	Cumulative Bottleneck	40	80	120	160	200	240
12
Sheet7

Cell Formulas
Range	Formula
B3:G3	B3	=MIN(B10:B11)-SUM($A3:A3)
H3,H5:H6	H3	=SUM(B3:G3)
B10:G10	B10	=SUM($B6:B6)
B11:G11	B11	=SUM($B5:B5)

 

[awoohaw]

I came up with this, but my Oct number is off by 1 for some reason: (I think your 31 is a typo).

Book1
	A	B	C	D	E	F	G
1
2
3
4
5		2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01	2024-12-01
6	Bottleneck	40	40	40	40	40	40
7	Before Bottleneck	69	28	36	17	21	35
8	Numbr to process	40	40	40	30	21	35
Sheet1

Cell Formulas
Range	Formula
C5:G5	C5	=EDATE(B5,1)
B8:G8	B8	=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))))

 

[overbomb]

I came up with this, but my Oct number is off by 1 for some reason: (I think your 31 is a typo).

Book1
	A	B	C	D	E	F	G
1
2
3
4
5		2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01	2024-12-01
6	Bottleneck	40	40	40	40	40	40
7	Before Bottleneck	69	28	36	17	21	35
8	Numbr to process	40	40	40	30	21	35
Sheet1

Cell Formulas
Range	Formula
C5:G5	C5	=EDATE(B5,1)
B8:G8	B8	=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

 

[overbomb]

Thank you for your replay but this hasn't worked as expected.

 

[awoohaw]

Please share your result that is not working. Just saying it doesn't work doesn't tell anyone where the error is. the xl2bb tool is excellent for helping you.