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!

 

[jdotwu]

Actually F4 should be 300, not 350.

 

[Marcelo Branco]

Another one (not very elegant - 3 different formulas...)

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

Formula in C2 copied down
=IF(100>SUMIF(A$1:A1,$A2,C$1:C1),MIN($B2,100-SUMIF(A$1:A1,$A2,C$1:C1)),"")

Formula in D2 copied down
=IF(AND($B2-N(C2)>0,100>SUMIF(A$1:A1,A2,D$1:D1)),MIN($B2-N(C2),100-SUMIF(A$1:A1,A2,D$1:D1)),"")

Formula in E2 copied down
=IF($B2-SUM($C2:D2)>0,$B2-SUM($C2:D2),"")

M.

 

[jdotwu]

This worked! Having three different formulas actually worked out for me because my actual data set has more than three tiers. I was able to manipulate the formula to get what I need. Thank you so much!

Another one (not very elegant - 3 different formulas...)

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

<tbody>
</tbody>

Formula in C2 copied down
=IF(100>SUMIF(A$1:A1,$A2,C$1:C1),MIN($B2,100-SUMIF(A$1:A1,$A2,C$1:C1)),"")

Formula in D2 copied down
=IF(AND($B2-N(C2)>0,100>SUMIF(A$1:A1,A2,D$1:D1)),MIN($B2-N(C2),100-SUMIF(A$1:A1,A2,D$1:D1)),"")

Formula in E2 copied down
=IF($B2-SUM($C2:D2)>0,$B2-SUM($C2:D2),"")

M.

 

[jdotwu]

Thank you! This worked for the sample data set, but when I tried it against my production data, I started getting errors. The reason I think is because my data set is not clean. I have columns between the tiers for other calculations. I think that's what messed it up. Regardless, user Marcelo Branco was able to give me a solution for my needs. Thanks for your help though!

	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​

<tbody>
</tbody>

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.

 

[Marcelo Branco]

This worked! Having three different formulas actually worked out for me because my actual data set has more than three tiers. I was able to manipulate the formula to get what I need. Thank you so much!

You are welcome.
Great that you have adapted the formulas to your real case. Well done!
Glad to help.

M..