Intermediate Excel Formula Help

Hello Everyone,

I'm working on creating a spreadsheet that will make quoting customers much easier. We use a column pricing strategy to quote our customers. It basically would look something like this:

100 = $1 per unit
1,000 = $.85 per unit
5,000 = $.70 per unit
10,000 = $.50 per unit

I'm trying to create a sheet where our sales reps can simply enter the quantity desired by the customer and the formula will automatically calculate the total cost.

I haven't really used excel in a couple of years but I know I used to be able to do this. Let's assume the desired quantity is in cell a1 and cell a2 will pull the price from a table based on the column pricing. I can't figure out how the if statement should be structured.

Thanks for all the help.

Is that $1 per unit up to 100 units, what would you charge for 101, $0.85?

simplest way is to create a lookup table, e.g. put the lower bound of each range in F2 down and the corresponding rates in G2 down, e.g. F2 = 0, G2 = 1, F3 = 101 G3 = 0.85 etc.

Now you can use a simple LOOKUP formula in A2 for total cost

=LOOKUP(A1,F2:G5)*A1

Maybe something like:

=INDEX($F$2:$J$4,MATCH($A2,$E$2:$E$4),MATCH($B2,$F$1:$J$1))

F2 to J4 contains the price matrix. E2 to E4 contains the product list. F1 to J1 contains the lower limits (1, 101, 1001, 5001, 10001). A2 is the product, B2 is the quantity and C2 is the price for that pairing.

HTML Code: 

Regards