Help! Creating a spreadsheet for products whos prices vary depending on quantity...

At work I'm asked to create a pricing spreadsheet for a list of products, and I'm stuck! I tried looking it up but after a couple of hours reading guides online...I am still unclear...

Here's my dilemma: How do create a formula for this:

Problem: There are some products on this price list that have different cost per item depending on the quantity.

So for example :

If purchasing 49 apples of less the cost of each apple is .10cents
if purchasing 50-100 apples the cost of each apple is .5 cents
If purchasing 101 apples of more, the cost of each apple is .01cent

...How do I create a formula will calculate based on the above conditions? (I've only done simpler excel formulas before like for ex., "=A1*B2"...

Somebody please help!

If you need me to provide more information please let me know.

Thank You

Assuming the quantity of apples to be in A2 and the desired result to be B2 try the formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

The formula is applied to B2 in the attached file. I also played around with a table as John has suggested and you might be interested in the formulas in cells J2:J3:

Please Login or Register  to view this content.

Price based on quantity.xlsx
Let me know if you have any questions.