Tier Fee Equation

[zmonaghan]

Hi -

Can anyone help me figure out how to calculate total gross on the table? I would like it to calculate $100,000 in first column, $22,500 in second, and $20,000 in third.

[TABLE="width: 369"]
<colgroup><col width="206" style="width: 155pt; mso-width-source: userset; mso-width-alt: 7533;"> <col width="143" style="width: 107pt;" span="2"> <tbody>[TR]
[TD="class: xl66, width: 492, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=9BC2E6]#9BC2E6[/URL] , colspan: 3"]Proposed Fees[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DDEBF7"]Vehicles Sold (Non-retroactive)[/TD]
[TD="class: xl65, bgcolor: #DDEBF7"]Seller's Fee[/TD]
[TD="class: xl65, bgcolor: #DDEBF7"]Total Gross[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]0 - 2000[/TD]
[TD="class: xl63, bgcolor: transparent"]$50[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2001 - 2500[/TD]
[TD="class: xl63, bgcolor: transparent"]$45[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2501 -3000[/TD]
[TD="class: xl63, bgcolor: transparent"]$40[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]> 3000[/TD]
[TD="class: xl63, bgcolor: transparent"]$35[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This will give you Separate and Total Gross hardcoding your tier in the formulae with a user assigned Number of Vehicles Sold in A9:

Book1
	A	B	C	D
1	Proposed Fees
2	Vehicles Sold (Non-retroactive)	Seller's Fee	Separate Gross	Total Gross
3	0 - 2000	$50	$100,000	$130,500
4	2001 - 2500	$45	$22,500
5	2501 -3000	$40	$8,000
6	> 3000	$35	$0
7
8	Number of Vehicles Sold
9	2700
Sheet35

Cell Formulas
Range	Formula
C3	=MIN(2000,A9)*B3
C4	=MIN(500,MAX(0,A9-2000))*B4
C5	=MIN(500,MAX(0,A9-2500))*B5
C6	=MAX(0,A9-3000)*B6
D3	=SUM(MIN(2000,A9)*B3,MIN(500,MAX(0,A9-2000))*B4,MIN(500,MAX(0,A9-2500))*B5,MAX(0,A9-3000)*B6)

This will give you Separate and Total Gross using a Table by separating your Tiers into two Columns, this gives you the flexibility of changing the requirements without having to change the formulae:

Book1
	A	B	C	D	E
12	Proposed Fees
13	Vehicles Sold (Non-retroactive)		Seller's Fee	Separate Gross	Total Gross
14	0	2000	$50	$100,000	$134,500
15	2001	2500	$45	$22,500
16	2501	3000	$40	$12,000
17	> 3000		$35	$0
18
19	Number of Vehicles Sold
20	2800
Sheet35

Cell Formulas
Range	Formula
D14	=MIN(B14,A20)*C14
D15	=MIN(B15-B14,MAX(0,A20-B14))*C15
D16	=MIN(B16-B15,MAX(0,A20-B15))*C16
D17	=MAX(0,A20-B16)*C17
E14	=SUM(MIN(B14,A20)*C14,MIN(B15-B14,MAX(0,A20-B14))*C15,MIN(B16-B15,MAX(0,A20-B15))*C16,MAX(0,A20-B16)*C17)

But I might be Overthinking it for you, and as your description in your post, you Only want the simple math:

Book1
	A	B	C
23	Proposed Fees
24	Vehicles Sold (Non-retroactive)	Seller's Fee	Total Gross
25	0 - 2000	$50	$100,000
26	2001 - 2500	$45	$22,500
27	2501 -3000	$40	$20,000
28	> 3000	$35
Sheet35

Cell Formulas
Range	Formula
C25	=2000*B25
C26	=500*B26
C27	=500*B27

 

[zmonaghan]

Thank you! This is perfect, splitting the tiers into 2 columns was a great idea.

 

[jtakw]

You're welcome, welcome to the forum, glad it helped.