"Formula that will calculate price based on quantiity"

  • Thread starter Thread starter Don Maines
  • Start date Start date
D

Don Maines

Trying to create a spread sheet that I will be able to present to a
customer on a sales call that will calculate price based on the
quantity of pieces bought.

Questions:

- Is there a formula that will allow me to do this?

- If so can I use one cell for the formula?

There are 8 groups of quantities

Thanks!!!
 
Hi
a little bit more detail would be helpful but lets assume the following
you have a list of quantities and prices in column A and B
A B
0 $11 -> that is for 0-9 pieces you pay $11
10 $10 -> if you order 10-19 pieces all will cost $10
20 $9
30 $8
.....

Column A reflects the quantity category and column B the respective
price. I'll assume that in your case the price is valid for the total
charge. That is if a customer buys 30 pieces all will cost $8.
Next assumption: you enter your quantity in cell C1. D1 will now
calculate the respective prive. Enter the following
=VLOOKUP(C1,A1:B8,2,TRUE)
In E1 calculate your total:
=C1*D1

HTH
Frank
 
Frank, Thanks for the information, I greatly appreciate it. You hit
homerun!

Sincerely,
Don Maine
 
Frank,

Here is more information, I am not sure if it will make a difference.


I have a base price of an item, the item is going to be silk-screened.
The price of the silk-screening is based on quantity and the amount o
colors in your logo. For example:

BASE QTY 1 COLOR 2 COLOR
$20.00 12-23 2.50 2.00
24-35 1.80 1.10

We offer 8 different groups of quantities and up to 6 colors.

Thanks for taking an interest and the help!

Do
 
Hi Don

so you have to do a 'double-lookup'. First searching the quantity and
afterwards the correct color column. I I understood your example
correctly, you have a base price (depending on the quantity) and an
additional cost factor for the number of colors. For some information
on these kind of double lookups have a look at
http://www.cpearson.com/excel/lookups.htm#DoubleLookup

If you like, send me an email at frank[.]kabel[at]freenet[dot]de and
I'll send you an working example for your case

Frank
 
Frank,

Tried to e-mail you for that working example of the double lookup.
went to the link you suggested and i have no clue what that means. A
you can see I am not a computer whiz.

Thanks,
Don

Frank, you can e-mail the example directly to me at (e-mail address removed)
 
Back
Top