Formula creation help - apply multiple conditions based on value

[Biking Loki]

First, I wasn't sure of the best way to title this post. Hopefully my description below will be more helpful.

I need to come up with a way to use excel to calculate a formula based on the following.

If an item cost $360,000.00 the following table below needs to be factored in to figure the commission (for lack of a better word).

(Rate per 1,000)
0-249,999.99 2.73
250,000-499,999.99 2.59
500,000-999,999.99 2.38
1,000,000-1,999,999.99 1.58

In my example, the 360,000 price would result in a 967.40 commission. That was calculated by adding (249,999.00/1,000) x 2.73 PLUS ((360,000-250,000)/10000) x 2.59.

Basically, I am looking for a way to put the pricevalue in Cell A1 and have the commission calculated on the table listed above. Is there a way to do that with excel?

 

[oldbrewer]

If an item cost $360,000.00 the following table below needs to be factored in to figure the commission (for lack of a better word).
(Rate per 1,000)
0-249,999.99 2.73
250,000-499,999.99 2.59
500,000-999,999.99 2.38
1,000,000-1,999,999.99 1.58
In my example, the 360,000 price would result in a 967.40 commission. That was calculated by adding (249,999.00/1,000) x 2.73 PLUS ((360,000-250,000)/10000) x 2.59.
													max
					360000	967.40					0	2.73
											250000	2.59	682.5	682.5
											500000	2.38	1295	1977.5
											1000000	1.58	2380	4357.5
											1000000	1.58	1580	5937.5
					formula
					=(((F17-VLOOKUP(F17,mytable,1))/1000)*VLOOKUP(F17,mytable,2))+VLOOKUP(F17,mytable,3)

<colgroup><col span="17"></colgroup><tbody>
</tbody>

 

[CalcSux78]

Nice one oldbrewer...

Here's an alternate method where you format as a table for the calculations.

Excel 2010
	A	B	C	D	E	F
1	$ 360,000.00		Min	Max	Rate	Paid
2			$ -	$ 249,999.99	$ 2.73	$ 682.50
3			$ 250,000.00	$ 499,999.99	$ 2.59	$ 284.90
4			$ 500,000.00	$ 999,999.99	$ 2.39	$ -
5			$ 1,000,000.00	$ 1,999,999.99	$ 1.58	$ -
6			Total			$ 967.40
Sheet1

Cell Formulas
Range	Formula
F2	=(MIN([@Max],A$1)-MIN([@Min],A$1))/1000*[@Rate]

 

[CalcSux78]

Quick note on post 3:

Range C1:F5 was formatted as a table and I added Total Row under the Table Tools Design Contextural tab.

 

[Biking Loki]

I am using Excel 2016. When I copy the formula into Cell F2, I get an error message about syntax. Is there something I am doing wrong in the set up?

Nice one oldbrewer...

Here's an alternate method where you format as a table for the calculations.

Excel 2010
	A	B	C	D	E	F
1	$ 360,000.00		Min	Max	Rate	Paid
2			$ -	$ 249,999.99	$ 2.73	$ 682.50
3			$ 250,000.00	$ 499,999.99	$ 2.59	$ 284.90
4			$ 500,000.00	$ 999,999.99	$ 2.39	$ -
5			$ 1,000,000.00	$ 1,999,999.99	$ 1.58	$ -
6			Total			$ 967.40
Sheet1

Cell Formulas
Range	Formula
F2	=(MIN([@Max],A$1)-MIN([@Min],A$1))/1000*[@Rate]

 

[CalcSux78]

Try this formula for F2:

=(MIN(Sheet1!$D2,A$1)-MIN(Sheet1!$C2,A$1))/1000*Sheet1!$E2

Then you should be able to drag the selection down to F5. (Note: you might need to change the sheet reference portion of code)