calculating price depending on quantity

[impresslb]

I am trying to use excel to estimate the price for printing for our small print shop.

We charge per 1000 sheets , and the charge per sheet will depend on how many thousands of sheets we are printing.

depending on the size etc... we have managed to calculate how many sheets need to be purchased and how many sheets need to be printed. But now we have a large excel file that has all the quantities of sheets that need to be printed for all the different sizes of jobs.

Now all we want is to make a formula thats says : if the number of sheets to be printed is 0 to 1000 sheets , then the cost per thousand is $60 usd , so anything less than 1000 sheets even if its 10 sheets will be charged at $60 .

from 1000 to 2000 the cost per 1000 is $35 , so 2000 sheets is $70

here is the entire sheet:
zero to 1000 $60.00 /1000
2000 $35.00 /1000
3000 $26.80 all below is per thousand
4000 $24.00
5000 $24.00
6000 $24.00
7000 $24.00
8000 $24.00
9000 $24.00
10000 $24.00
15000 $20.00
20000 $16.00
25000 $16.00
50000 $16.00
100000 $14.00

I tried doing a look up table that looks like this :
=LOOKUP(J44,{0,1000,2000,3000,4000,5000,6000,7000,8000,9000,10000,15000,2000,25000,50000},{15,8.75,7.5,7,7,7,7,7,7,6,5,4,4,4})

(dont look at the values on the second part the USD amounts are not right but i was talking about the idea.

I just feel there must me an easier way.....please help.

I also want to make sure also that there is no usd value less than $60 , cause that is our minimum price no matter how small the print job us.

[impresslb]

done. Is this title fine ?

[arthurbr]

There is no change...

[impresslb]

sorry, i am new , i typed the new title in the "reason for change of title" box.

Now it should be fine.

[TMS]

You don't need all the numbers, only those where the rate changes.

Something like:

=LOOKUP(B2,{0,1000,2000,5000,10000},{60,35,24,20,16})

Regards

[impresslb]

thanks very much. How do i make sure the minimum of $60 is met ?

[TMS]

I don't understand the question. If you put a value in that is less than a 1000, you will get $60.

Regards

[Alf]

Question, should not the formula be:

=LOOKUP(B2,{0,1001,2001,5001,10001},{60,35,24,20,16})

Alf

[TMS]

I suspect that you are correct. It rather depends on where the OP wants the breaks.

Good spot.

Regards