how to do this sumproduct calculation?

[bigdan]

I'm guessing this can be solved with a sumproduct but I really don't know for sure.


[TABLE="width: 500"]
<tbody>[TR]
[TD]0-5KM[/TD]
[TD]50%[/TD]
[TD]$5[/TD]
[/TR]
[TR]
[TD]5-10KM[/TD]
[TD]15%[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]10-15KM[/TD]
[TD]15%[/TD]
[TD]$15[/TD]
[/TR]
[TR]
[TD]15-20KM[/TD]
[TD]10%[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]20-30KM[/TD]
[TD]5%[/TD]
[TD]$25[/TD]
[/TR]
[TR]
[TD]30-40KM[/TD]
[TD]5%[/TD]
[TD]$25[/TD]
[/TR]
</tbody>[/TABLE]


Here's a table which shows how much we charge (col C) for various distances. Col B shows typically what percentage of our orders falls under which range.

I'd like to take an Order Vol, say 100K orders, and see what the results would be. So I'd like a formula to be something like:
100K * 50% * $5 + 100K * 15% * $10 + 100K * 15% * $15 etc

Is there an easier way to do this?

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	E
1	0-5KM	50%	$5
2	5-10KM	15%	$10		1,075,000.00
3	10-15KM	15%	$15
4	15-20KM	10%	$20
5	20-30KM	5%	$25
6	30-40KM	5%	$25
Sheet

 

[bigdan]

Thanks so much!