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!

 

[jdotwu]

Actually F4 should be 300, not 350.

 

[Marcelo Branco]

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


[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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Amount​

[/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][/td][td][/td][/tr]

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

3​

[/td][td]

Bob​

[/td][td]

250​

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

100​

[/td][td]

150​

[/td][/tr]

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

4​

[/td][td]

Bob​

[/td][td]

300​

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

300​

[/td][/tr]

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

5​

[/td][td]

Carl​

[/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]

40​

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

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

7​

[/td][td]

Carl​

[/td][td]

50​

[/td][td]

50​

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

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

8​

[/td][td]

Carl​

[/td][td]

110​

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

100​

[/td][td]

10​

[/td][/tr]

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

9​

[/td][td]

Carl​

[/td][td]

200​

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

200​

[/td][/tr]

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

10​

[/td][td]

David​

[/td][td]

1000​

[/td][td]

100​

[/td][td]

100​

[/td][td]

800​

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

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...)


[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]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Amount​

[/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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Bob​

[/TD]
[TD]

250​

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

100​

[/TD]
[TD]

150​

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

4​

[/TD]
[TD]

Bob​

[/TD]
[TD]

300​

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

300​

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

5​

[/TD]
[TD]

Carl​

[/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]

40​

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

7​

[/TD]
[TD]

Carl​

[/TD]
[TD]

50​

[/TD]
[TD]

50​

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

8​

[/TD]
[TD]

Carl​

[/TD]
[TD]

110​

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

100​

[/TD]
[TD]

10​

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

9​

[/TD]
[TD]

Carl​

[/TD]
[TD]

200​

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

200​

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

10​

[/TD]
[TD]

David​

[/TD]
[TD]

1000​

[/TD]
[TD]

100​

[/TD]
[TD]

100​

[/TD]
[TD]

800​

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


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!

[TABLE="class: grid"]
<tbody>[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]
</tbody>[/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.

 

[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..