How do I calculate total commission based on a dollar rate per unit sold within a range?

[MXL]

Greetings,

I want to know how I can calculate total commission based on total units sold. For example, if I sold 137 units illustrated below, there is a commission rate per unit sold for each range. The first 75 sold would be multiplied by $1.05 = $78.7. The next 45 fall within the 76 to 120 range multiplied by $1.35 = $60.8 and there are 17 units remaining to be multiplied by $1.75 = $29.8. Total commission therefore would = $169.3 for the sale of 137 units.

	A	B	C	D	E	F
1	Unit Range	Lower Range	Upper Range	Commission Rate per Unit Sold	Cumulative Commission	Total Sales (Units)
2	1 to 75	1	75	$1.05	$78.7	137
3	76 to 120	76	120	$1.35	$60.8
4	Over 121	121		$1.75	$29.8	Total Commission
5						$169.3​

<tbody> </tbody>

<----- Formula I need


How can I put this in one formula in cell F5?

Thank you for taking the time to read this, much appreciated. Look forward to hearing from you all.

Regards,

Vladmir

 

[Michael M]

I think the answer should be $169.25

Excel 2003
	A	B	C	D	E	F
1	Unit Range	Lower Range	Upper Range	Commission Rate per Unit Sold	Cumulative	Total Sales
2	1 to 75	1	75	$1.05	$78.70	137
3	76 to 120	76	120	$1.35	$60.80
4	Over 121	121		$1.75	$29.80	Total Commission
5						$169.25
Sheet1

Cell Formulas
Range	Formula
F5	=IF(F2F2C2*D2)+((F2-C2)*D3),(C2*D2)+((C3-C2)*D3)+((F2-B4+1)*D4)))

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1	Unit Range	Commission Rate per Unit Sold	Rate Differential			Total Sales
2	0	$1.05	1.05			137
3	75	$1.35	0.30
4	120	$1.75	0.40			Total Commission
5						$169.25
6
3b

Excel 2003

Cell Formulas
Range	Formula
C2	=B2-N(B1)
C3	=B3-N(B2)
C4	=B4-N(B3)
F5	=SUMPRODUCT(--(F2>A2:A4),F2-A2:A4,C2:C4)

 

[Michael M]

Hi Dave.
I was thinking along the same lines, but ithought the OP might have good reason to keep their layout......

 

[MXL]

thank you Michael M & Dave Patton, truly ingenious. I appreciate it.

 

[Michael M]

Daves reply will be more efficient, if you're happy to change your layout....thanks for the feedback...