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

Name	Amount
Bob	100
Bob	250
Bob	300
Carl	10
Carl	40
Carl	50
Carl	110
Carl	200
David	1000

<tbody>
</tbody>

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

Name	Amount	Tier 1	Tier 2	Tier 3
Bob	100	100
Bob	250		250
Bob	300		300
Carl	10	10
Carl	40	40
Carl	50	50
Carl	110		100	10
Carl	200			200
David	1000	100	100	800

<tbody>
</tbody>

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?

Name​	Amount​	Tier 1​	Tier 2​	Tier 3​
Bob​	100​	100​	0​	0​
Bob​	250​	100​	100​	50​
Bob​	300​	100​	100​	100​
Carl​	10​	10​	0​	0 ​
Carl​	40​	40​	0​	0​
Carl​	50​	50​	0​	0​
Carl​	110​	100​	10​	0​
Carl​	200​	100​	100​	0​
David​	1000​	100​	100​	800​

<tbody>
</tbody>

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.

Name	Amount	Running Total
Bob	100	100
Bob	250	350
Bob	300	650
Carl	10	10
Carl	40	50
Carl	50	100
Carl	110	210
Carl	200	410
David	1000	1000

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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':

	Amount	Tier 1	Tier 2	Tier 3
Bob	100	100
Bob	250		100	150
Bob	300			300

<tbody>
</tbody>

 

[jdotwu]

The end result should look like this.

Name	Amount	Tier 1	Tier 2	Tier 3
Bob	100	100
Bob	250		250
Bob	300		300
Carl	10	10
Carl	40	40
Carl	50	50
Carl	110		100	10
Carl	200			200
David	1000	100	100	800

<tbody>
</tbody>

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:

Name	Amount	Running Total	Tier 1	Tier 2	Tier 3		Name	Tier 1 Reason	Tier 2 Reason	Tier 3 Reason
Bob	90	90	90				Bob	Tier 1 threshold has $100 left
Bob	400	490	10	100	290		Bob	Tier 1 threshold has $10 left	Tier 2 theshold has $100 left	Remaining Amount to Balance $400
Bob	50	540			50		Bob			Both Tier 1 and Tier 2 threshold has already been met
Carl	10	10	10				Carl	Tier 1 threshold has $100 left

<colgroup><col span="2"><col><col span="5"><col><col><col></colgroup><tbody>
</tbody>

 

[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':

	Amount	Tier 1	Tier 2	Tier 3
Bob	100	100
Bob	250		100	150
Bob	300			300

<tbody>
</tbody>

 

[Marcelo Branco]

So, i think, we now know the expected results

	A​	B​	C​	D​	E​	F​
1​	Name​	Amount​	Running Total​	Tier 1​	Tier 2​	Tier 3​
2​	Bob​	100​	100​	100​
3​	Bob​	250​	350​		100​	150​
4​	Bob​	300​	650​			350​
5​	Carl​	10​	10​	10​
6​	Carl​	40​	50​	40​
7​	Carl​	50​	100​	50​
8​	Carl​	110​	210​		100​	10​
9​	Carl​	200​	410​			200​
10​	David​	1000​	1000​	100​	100​	800​

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

M.

 

[jdotwu]

Yep! This is it!

So, i think, we now know the expected results

	A​	B​	C​	D​	E​	F​
1​	Name​	Amount​	Running Total​	Tier 1​	Tier 2​	Tier 3​
2​	Bob​	100​	100​	100​
3​	Bob​	250​	350​		100​	150​
4​	Bob​	300​	650​			350​
5​	Carl​	10​	10​	10​
6​	Carl​	40​	50​	40​
7​	Carl​	50​	100​	50​
8​	Carl​	110​	210​		100​	10​
9​	Carl​	200​	410​			200​
10​	David​	1000​	1000​	100​	100​	800​

<tbody>
</tbody>

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

M.

 

[shg]

	A​	B​	C​	D​	E​	F​
1​				100​	100​	1000000​
2​	Name​	Amount​	DoNotUse​	Tier1​	Tier2​	Tier3​
3​
4​	Bob	100​		100​	0​	0​
5​	Bob	250​		0​	100​	150​
6​	Bob	300​		0​	0​	300​
7​	Carl	10​		10​	0​	0​
8​	Carl	40​		40​	0​	0​
9​	Carl	50​		50​	0​	0​
10​	Carl	110​		0​	100​	10​
11​	Carl	200​		0​	0​	200​
12​	David	1000​		100​	100​	800​

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.