Running Total Formula with Multiple Tiers

[jdotwu]

Hoping someone could help me out with a formula or macro to solve my issue.

I currently have a list of data that's listed as such
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]

What I need to do is calculate the commission of each person but there are tiers.
Tier 1 (0-100) = 5%
Tier 2 (101 - 200) = 10%
Tier 3 (>201) = 20%

But it's always a running total. So I expect the end result to look like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[TD]Tier 1[/TD]
[TD]Tier 2[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]110[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]1000[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]

I can hard code the commission percentages next to each tier and sum at the end for total commission, but the part I do not know how to do, is how to get the numbers to populate in columns Tier 1, Tier 2 and Tier 3. I am currently doing this manually for about 300-400 rows and think that there must be an easier way.

Any help is appreciated!

 

[Marcelo Branco]

I'm not understanding the results you showed to Bob 250 and Carl 110

Shouldn't the results be like this?

[TABLE="class: grid"]
<tbody>[TR]
[TD]

Name​

[/TD]
[TD]

Amount​

[/TD]
[TD]

Tier 1​

[/TD]
[TD]

Tier 2​

[/TD]
[TD]

Tier 3​

[/TD]
[/TR]
[TR]
[TD]

Bob​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

Bob​

[/TD]
[TD]

250​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

50​

[/TD]
[/TR]
[TR]
[TD]

Bob​

[/TD]
[TD]

300​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD]

Carl​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[TD]

0
​

[/TD]
[/TR]
[TR]
[TD]

Carl​

[/TD]
[TD]

40​

[/TD]
[TD]

40​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

Carl​

[/TD]
[TD]

50​

[/TD]
[TD]

50​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

Carl​

[/TD]
[TD]

110​

[/TD]
[TD]

100​

[/TD]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

Carl​

[/TD]
[TD]

200​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

David​

[/TD]
[TD]

1000​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

800​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[jdotwu]

Sorry, I should've clarified. Because Bob 250 has already met the threshold for Tier 1, all of his subsequent payments will start with Tier 2.

Same for Carl 110. Carl 10, 40 and 50 met the Tier 1 limit. So Carl 110 starts on Tier 2.

I've since added a running total column. I know I need something like this to make this work, just not sure how everything fits together yet.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: center"]Name[/TD]
[TD="class: xl63, width: 64, align: center"]Amount[/TD]
[TD="width: 64, align: center"]Running Total[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"] [/TD]
[TD="class: xl63, width: 64, align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Bob[/TD]
[TD="class: xl63, width: 64, align: center"]100[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Bob[/TD]
[TD="class: xl63, width: 64, align: center"]250[/TD]
[TD="align: center"]350[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Bob[/TD]
[TD="class: xl63, width: 64, align: center"]300[/TD]
[TD="align: center"]650[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Carl[/TD]
[TD="class: xl63, width: 64, align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Carl[/TD]
[TD="class: xl63, width: 64, align: center"]40[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Carl[/TD]
[TD="class: xl63, width: 64, align: center"]50[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Carl[/TD]
[TD="class: xl63, width: 64, align: center"]110[/TD]
[TD="align: center"]210[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]Carl[/TD]
[TD="class: xl63, width: 64, align: center"]200[/TD]
[TD="align: center"]410[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: center"]David[/TD]
[TD="class: xl63, width: 64, align: center"]1000[/TD]
[TD="align: center"]1000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Still not clear (at least for me)
So what are the expected results to Bob 250, Carl 110, David 1000 etc for each tier?
Please, could you confirm if the expected results still are those you showed in post #1 ?
If not, show us the correct expected results.

M.

 

[Greg Truby]

Still not clear (at least for me)

M.

Me neither. I woulda thought ol' Bob'd be gettin':

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]Amount[/TD]
[TD="class: xl68, width: 64"]Tier 1[/TD]
[TD="class: xl66, width: 64"]Tier 2[/TD]
[TD="class: xl68, width: 64"]Tier 3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Bob[/TD]
[TD="class: xl67, width: 64, align: right"]100[/TD]
[TD="class: xl69, align: right"]100[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Bob[/TD]
[TD="class: xl67, width: 64, align: right"]250[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl69, align: right"]150[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Bob[/TD]
[TD="class: xl67, width: 64, align: right"]300[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl69, align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]

 

[jdotwu]

The end result should look like this.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[TD]Tier 1[/TD]
[TD]Tier 2[/TD]
[TD]Tier 3[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]250[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]110[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]1000[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]


Let's walk through Bob 250 as an example. The tiers are 0-100, 100-200 and >200.
Bob 100 has already fully satisfied the threshold for tier 1. So when Bob 250 hits, he gets to start on Tier 2 right away, because Bob 100 satisfied Tier 1.

To simplify, let's imagine this:

[TABLE="width: 1271"]
<colgroup><col span="2"><col><col span="5"><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Amount[/TD]
[TD]Running Total[/TD]
[TD]Tier 1[/TD]
[TD]Tier 2[/TD]
[TD]Tier 3[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Tier 1 Reason[/TD]
[TD]Tier 2 Reason[/TD]
[TD]Tier 3 Reason[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]Tier 1 threshold has $100 left[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]290[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]Tier 1 threshold has $10 left[/TD]
[TD]Tier 2 theshold has $100 left[/TD]
[TD]Remaining Amount to Balance $400[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]540[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Both Tier 1 and Tier 2 threshold has already been met[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Carl[/TD]
[TD]Tier 1 threshold has $100 left[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[jdotwu]

Sorry! You're right! Those are the numbers for Bob. I made an error when making up data.

But this is exactly why I want to automate this. Working on hundreds of rows a month, it's very easy to manually input the wrong amount like I did for Bob.

Me neither. I woulda thought ol' Bob'd be gettin':

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]Amount[/TD]
[TD="class: xl68, width: 64"]Tier 1[/TD]
[TD="class: xl66, width: 64"]Tier 2[/TD]
[TD="class: xl68, width: 64"]Tier 3[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Bob[/TD]
[TD="class: xl67, width: 64, align: right"]100[/TD]
[TD="class: xl69, align: right"]100[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Bob[/TD]
[TD="class: xl67, width: 64, align: right"]250[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl69, align: right"]150[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]Bob[/TD]
[TD="class: xl67, width: 64, align: right"]300[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl69, align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

So, i think, we now know the expected results


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Amount​

[/td][td]

Running Total​

[/td][td]

Tier 1​

[/td][td]

Tier 2​

[/td][td]

Tier 3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Bob​

[/td][td]

100​

[/td][td]

100​

[/td][td]

100​

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

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][td]

250​

[/td][td]

350​

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

100​

[/td][td]

150​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Bob​

[/td][td]

300​

[/td][td]

650​

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

350​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Carl​

[/td][td]

10​

[/td][td]

10​

[/td][td]

10​

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

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Carl​

[/td][td]

40​

[/td][td]

50​

[/td][td]

40​

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

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Carl​

[/td][td]

50​

[/td][td]

100​

[/td][td]

50​

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

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Carl​

[/td][td]

110​

[/td][td]

210​

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

100​

[/td][td]

10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Carl​

[/td][td]

200​

[/td][td]

410​

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

200​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

David​

[/td][td]

1000​

[/td][td]

1000​

[/td][td]

100​

[/td][td]

100​

[/td][td]

800​

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

Are really these?
Thinking how to create a formula...

M.

 

[jdotwu]

Yep! This is it!

So, i think, we now know the expected results


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Amount​

[/TD]
[TD]

Running Total​

[/TD]
[TD]

Tier 1​

[/TD]
[TD]

Tier 2​

[/TD]
[TD]

Tier 3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Bob​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Bob​

[/TD]
[TD]

250​

[/TD]
[TD]

350​

[/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD]

150​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Bob​

[/TD]
[TD]

300​

[/TD]
[TD]

650​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

350​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Carl​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Carl​

[/TD]
[TD]

40​

[/TD]
[TD]

50​

[/TD]
[TD]

40​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Carl​

[/TD]
[TD]

50​

[/TD]
[TD]

100​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Carl​

[/TD]
[TD]

110​

[/TD]
[TD]

210​

[/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Carl​

[/TD]
[TD]

200​

[/TD]
[TD]

410​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

200​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

David​

[/TD]
[TD]

1000​

[/TD]
[TD]

1000​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

800​

[/TD]
[/TR]
</tbody>[/TABLE]


Are really these?
Thinking how to create a formula...

M.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

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

100​

[/td][td]

100​

[/td][td]

1000000​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Name​

[/td][td="bgcolor:#F3F3F3"]

Amount​

[/td][td="bgcolor:#F3F3F3"]

DoNotUse​

[/td][td="bgcolor:#F3F3F3"]

Tier1​

[/td][td="bgcolor:#F3F3F3"]

Tier2​

[/td][td="bgcolor:#F3F3F3"]

Tier3​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

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

100​

[/td][td][/td][td="bgcolor:#E5E5E5"]

100​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

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

250​

[/td][td][/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

100​

[/td][td="bgcolor:#E5E5E5"]

150​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

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

300​

[/td][td][/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

300​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

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

10​

[/td][td][/td][td="bgcolor:#E5E5E5"]

10​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

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

40​

[/td][td][/td][td="bgcolor:#E5E5E5"]

40​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

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

50​

[/td][td][/td][td="bgcolor:#E5E5E5"]

50​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

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

110​

[/td][td][/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

100​

[/td][td="bgcolor:#E5E5E5"]

10​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

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

200​

[/td][td][/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

200​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

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

1000​

[/td][td][/td][td="bgcolor:#E5E5E5"]

100​

[/td][td="bgcolor:#E5E5E5"]

100​

[/td][td="bgcolor:#E5E5E5"]

800​

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

In D4 and copied across and down,

=MIN(SUMIF($A$3:$A4, $A4, $B$3:B4) - SUMPRODUCT(($A$3:$A3 = $A4) * $C$3:$F3) - SUM($C4:C4), D$1 - SUMIF($A$3:$A3, $A4, D$3:D3))

D1 and E1 are the size of the tiers. F1 is an arbitrarily large number. Row 3 and col C blank.