an iterative calculation -- sort of
 

hello

i have values

10
15
20
30
40

....from which i want to subtract 30, starting with the first number and
iterate through my list until i've completed the sum. so for example i would
eliminte the 10, 15 and part of of the 20 to be left with:

0
0
15
30
40

This is the first part of a bigger problem and ideally i'd like to attempt
it without any vba. is this even possible ??


thanks
ali

an iterative calculation -- sort of
 

On Thu, 30 Jul 2009 17:34:35 +0100, "ali"
wrote:

hello

i have values

10
15
20
30
40

...from which i want to subtract 30, starting with the first number and
iterate through my list until i've completed the sum. so for example i would
eliminte the 10, 15 and part of of the 20 to be left with:

0
0
15
30
40

This is the first part of a bigger problem and ideally i'd like to attempt
it without any vba. is this even possible ??


thanks
ali

If you values are in column A starting on row 2 (not row 1)
try the following formula in cell B2.
Make sure that cells A1 and B1 are blank.

=A2-MIN(A2,30-SUM(A$1:A1)+SUM(B$1:B1))

Copy this formula down in column B as far as you have column A data.

Hope this helps / Lars-Åke

an iterative calculation -- sort of
 

Try this, sort of...

=A2-MIN(MAX(30-SUM(A$1:A1),0),A2)

here I assume there is not number in a1 and that the data starts in A2.
Fill down.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ali" wrote:

hello

i have values

10
15
20
30
40

....from which i want to subtract 30, starting with the first number and
iterate through my list until i've completed the sum. so for example i would
eliminte the 10, 15 and part of of the 20 to be left with:

0
0
15
30
40

This is the first part of a bigger problem and ideally i'd like to attempt
it without any vba. is this even possible ??


thanks
ali

an iterative calculation -- sort of
 

Shane Devenshire wrote...
Try this, sort of...

=A2-MIN(MAX(30-SUM(A$1:A1),0),A2)

here I assume there is not number in a1 and that the data starts in A2. *
Fill down.

Or just make B2

=MIN(MAX(SUM(A$2:A2)-30,0),A2)

and fill down. This doesn't depend on A1 being empty.

an iterative calculation -- sort of
 

thanks everyone, I'm at home now & don't have xl, but will try at work
tomorrow.

thanks again

ali



"Harlan Grove" wrote in message
...
Shane Devenshire wrote...
Try this, sort of...

=A2-MIN(MAX(30-SUM(A$1:A1),0),A2)

here I assume there is not number in a1 and that the data starts in A2.
Fill down.

Or just make B2

=MIN(MAX(SUM(A$2:A2)-30,0),A2)

and fill down. This doesn't depend on A1 being empty.