Subtract a value from list

[Guest]

have you data start in row 2, so

A2: 11,11
A3: 33,22

in C1 put in =22
in C2 put in the formula
=A2-MIN(A2,MAX($C$1-SUM($A$1:A1),0))

then drag fill it down column C to match your data.

Now, when you want to subtract additional, you can do

C1: =22+30

and the remainders will update.

You could adjust the formula in c1 to be

=Sum(D1:IV1)

then
D1: 22
E1: 30
and so forth

 

[David Biddulph]

No, I'm sorry, I don't understand your subtractions. Can you explain how C
is calculated from A?

 

[Rodrigo Ferreira]

I don't know how can I explain... I'll try:

I have a list like this:
A
11,11
33,22
11,55
66,55

And I have to subtract 22 from this list and I want a result like this:

C
0
22,33
11,55
66,55

After, I have to subtract 30 from this new list and I want a result like
this:
D
0
0
3,88
66,55

....

My list has more a lot of lines. And maybe I'll have to subtract more
values...

1- How can I calculate the column "D"?
2- Can I have a one column with this results? Without the column "C"?

Sorry for my poor english

 

[Rodrigo Ferreira]

Thanks Tom!!!!!!!!!

--

Rodrigo Ferreira

have you data start in row 2, so

A2: 11,11
A3: 33,22

in C1 put in =22
in C2 put in the formula
=A2-MIN(A2,MAX($C$1-SUM($A$1:A1),0))

then drag fill it down column C to match your data.

Now, when you want to subtract additional, you can do

C1: =22+30

and the remainders will update.

You could adjust the formula in c1 to be

=Sum(D1:IV1)

then
D1: 22
E1: 30
and so forth

 

[David Biddulph]

You say "What can I do????".
What you can do is explain how you want your numbers calculating. Tom's
formula assumed that you will subtract from each cell in turn down the
column, with each cell going down either to zero, or to what is needed to
reduce the total by the amount in your C1 cell. If that's not what you
want, you'll need to explain what you do want. How do you calculate your
value of 2,572424916 for C7? You seem to be suggesting that it should be
A7-C7, but it's C7 that you're calculating. If you put 2,572424916 in C7,
you've reduced the total of rows 2 to 7 by 55,96198416, not by 51,5953324
which was what you put in C1.

 

[Rodrigo Ferreira]

Tom,
Look this:

A2=1
A3=2
A4=3
A5=4
A6=5
A7=6

C1 = 4
Your formula works!
But

A2=10
A3=9,900990099
A4=9,806212457
A5=9,705901479
A6=9,609803445
A7=9,5115016

C1= 51,5953324

The formula return wrong answer:

C2=0
C3=0
C4=0
C5=0
C6=0
C7=6,939076684

C7 has to be 2,572424916 (A7-C7)

What can I do???? Please, help!

 

[Rodrigo Ferreira]

David
I was trying to reduce from each cell going down to zero.

Tom,
I was wrong, your formula works fine... Sorry Tom. My last calc was wrong.
Thanks Tom