subtract remaining number from different cell and 'remember' it

[Marc Mostert]

Hello guys,

Trying to subtract a number from a cell until it reaches 0, then subtract the remaining from a different cell and keep that number for the next time.

A1 has a value of 50, that is the spare amount
A2 has a value of 30, that is the total amount
then in B2 I have a value of 10
b3 a value of 10
b4 a value of 11

then B5 should say 0 and not -1 (the total of 30-10-10-11) and B6 should say 40 (the spare-1)

C2 has a value of 10
c3 has a value of 1
c4 has a value of 4

then C5 should say 49 (because the spare is not used this time)

D2 has a value of 10
D3 has a value of 15
D4 has a value of 15

Then D5 should have a of 39 (the spare is used again because it sums up to 40 which is 10 more then the total amount, but it should subtract form 49.)

I hope someone can help me set this up and I hope you understand what i mean, it's much clearer in my head ;P

Regards, Marc

 

[StephenCrump]

Welcome to the Forum

Like this?

B5: =MAX(0,$A2-SUM(B2:B4)) copy across
B6: =MAX(0,$A1+MIN(0,$A2-SUM(B2:B4)))
C6: =MAX(0,B6+MIN(0,$A2-SUM(C2:C4))) copy across

Book1
	A	B	C	D	E
1	50
2	30	10	10	10	25
3		10	1	15	25
4		11	4	15	25
5		0	15	0	0
6		49	49	39	0
Sheet1