Subtracting a number until 0 then go on to next row

[NTNTNT]

Hi,

I have question:

For example I have these numbers:

[TABLE="width: 450"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]15000[/TD]
[TD]10000[/TD]
[TD]125000[/TD]
[TD]15000[/TD]
[/TR]
</tbody>[/TABLE]

And I have a decrease of 40000 on these categories and I want to subract this number from the first category (A), and then subtract what is left on the second category (B) until the decrease of 40000 became 0.

How can I model this in a function?

Kind Regards,

NTNTNT

 

[123rickfear]

so would A become 0, B become 0, then C become 5000, and D & E remain the same?

 

[NTNTNT]

so would A become 0, B become 0, then C become 5000, and D & E remain the same?

Exactly

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]

20,000​

[/td][td]

15,000​

[/td][td]

10,000​

[/td][td]

125,000​

[/td][td]

15,000​

[/td][td]

40,000​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

40,000​

[/td][td]

-​

[/td][td]

-​

[/td][td]

5,000​

[/td][td]

125,000​

[/td][td]

15,000​

[/td][td]

40,000​

[/td][td]B2 and across: =MAX(0, SUM($A1:B1) - SUM($A2:A2))[/td][/tr]
[/table]

 

[123rickfear]

in your example assuming the data is in row 1, and that your number of 40000 is in G1

in a2 =IF(SUM($A$1:A1)<$G$1,"0",SUM($A1:$C1)-$G$1)

in b2 =IF(SUM($A$1:A1)>$G$1,B1,IF(SUM($A$1:B1)<$G$1,"0",SUM($A1:$C1)-$G$1)) and drag along.

 

[NTNTNT]

Thank you very much both!

I am trying to implement this in my excel but both formula's do not work. I am working in Excel 2013, could it be that things differ? Is it possible to send me your xls file?

 

[shg]

The formula will work in any version of Excel, and this forum does not support posting attachments.

 

[NTNTNT]

Thanks. The following error is showing when I try both methods.

 

[NTNTNT]

Ah I got it, the comma is a ; for me. Other settings I guess.

 

[Swami Kastur]

But if the values are in column then what would be the formula. Say I had A1 10000 ; B1 15000 ; C1 10000 ... so on till Z1 10000. And if I write in C1 50000 then A1 should be 0 ; B1 should be 0; until 50000 complete. Again in C2 i have 20000 then the remaining from 1st column should get subtracted the carry on the formula down.

Kindly help. If my query is not clear please buzz me, i'll try to simplify more.

Regards
Swami Kastur