How to calculate through different tiers?

[argomoo]

This is probably really simple for you experts out there. I'm just a
idiot.

I have a column of numerical data that I want to subtract from goin
from the bottom up. For instance on the example below, I would b
interested in subtracting 150, starting from level 6 moving upwards.
would want to find a return value of 0 for level 6 and 45 for level 5.
All the other levels would return their original values in thi
instance because the amount subtracted does not reach them. Hope thi
makes some sense. Thanks for any help in advance.

LEVEL Value
1 100
2 200
3 150
4 75
5 85
6 110
sum 720

Dav

 

[Frank Kabel]

Hi
if you current values are stored in A1:B7 (row one as
heading, row 7 for level 6) enter the following in C6
=MAX(B6-150,0)
in C5 enter
=MAX(B5-MAX(150-SUM($B6:$B$6),0),0)
and copy this upbwards

 

[argomoo]

Hey Frank,

Thanks a bunch for the help. Your advice was spot on. Saves me oodle
of time!

Dav

 

[argomoo]

Hello Excel Forum,

The original response was very helpful, unfortunately I have found tha
I need to add another function to the same set of data.

In my original problem I had the following data:

LEVEL Value
1 100
2 200
3 150
4 75
5 85
6 110

sum 720

I wanted to be able to subtract 150 from the sum, and subtract the 15
going upwards through the levels.

Now I would like to be able to have another variable to subtract fro
the from the top going down. For example, I would like to subtract 15
from bottom up and 110 from top down. Results would be the followin
if set up correctly:

LEVEL Value
1 0
2 190
3 150
4 75
5 45
6 0

Thanks in advance for any responses.

Davi

sum 46