I can't really figure out an efficient way to do this. I keep creating
nested if statements that are very long and annoying, but there has to be an
easier way.

Column A is quantity sold and is a numerical value anywhere between 1 and
2000, and in Column B I want to calculate total cost based on this price
scale --

Pricing
1 - 10 = $2.40
11- 100 = $1.30
101 - 250 = $.60
251 - 1000 = $.20
1001 + = .12

So if someone sells 290 the first 10 are priced at $2.40, the next 90 are
priced at $1.30, the next $150 are priced at $.60 and the final 40 are priced
at $.20. So the total price of selling 290 = $239.

Is there an easy way to set the price table in and have a small formula
calculate and add the breaks?

Have a good day,

Ted

=LOOKUP(A1,{1,11,101,251,1001},{2.4,1.3,0.6,0.2,0. 12})


"Ted Metro" wrote:

I can't really figure out an efficient way to do this. I keep creating
nested if statements that are very long and annoying, but there has to be an
easier way.

Column A is quantity sold and is a numerical value anywhere between 1 and
2000, and in Column B I want to calculate total cost based on this price
scale --

Pricing
1 - 10 = $2.40
11- 100 = $1.30
101 - 250 = $.60
251 - 1000 = $.20
1001 + = .12

So if someone sells 290 the first 10 are priced at $2.40, the next 90 are
priced at $1.30, the next $150 are priced at $.60 and the final 40 are priced
at $.20. So the total price of selling 290 = $239.

Is there an easy way to set the price table in and have a small formula
calculate and add the breaks?

Have a good day,

Ted

Hi Ted,

Look he

http://www.mcgimpsey.com/excel/variablerate.html


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ted Metro" wrote in message ...
|I can't really figure out an efficient way to do this. I keep creating
| nested if statements that are very long and annoying, but there has to be an
| easier way.
|
| Column A is quantity sold and is a numerical value anywhere between 1 and
| 2000, and in Column B I want to calculate total cost based on this price
| scale --
|
| Pricing
| 1 - 10 = $2.40
| 11- 100 = $1.30
| 101 - 250 = $.60
| 251 - 1000 = $.20
| 1001 + = .12
|
| So if someone sells 290 the first 10 are priced at $2.40, the next 90 are
| priced at $1.30, the next $150 are priced at $.60 and the final 40 are priced
| at $.20. So the total price of selling 290 = $239.
|
| Is there an easy way to set the price table in and have a small formula
| calculate and add the breaks?
|
| Have a good day,
|
| Ted