#### Latest posts by techwriter (see all)

- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
- 3 Ways to Add Copyright Free Images to Your Blogs, Books and Documents - September 19, 2016
- How to Delete All Hyperlinks in a MS #Word Document through VBA Macro - September 1, 2016

© Ugur Akinci

To reward the customer with quantity discounts for bulk purchases is a common practice in business.

Consider the below situation:

There are **FOUR different IF statements** here illustrated by the above table:

**(1)** Here the seller is charging $10 per item if the customer purchases 100 or less items.

**(2)** If the customer buys more than 100 but less than 501 items, than the price per item drops down to $6.

**(3)** If the customer buys more than 500 but less than 1001 items, per price shrinks to $3.

**(4)** If the customer buys more than 1000 items, the cost drops further down to just $1.

We can calculate the total price for a purchase by making the following **four comparisons** one after the another:

**(1)** Is the total quantity purchased less than or equal to 100? If so, the total cost is ORDER QUANTITY multiplied by $10. If not, move on to the next comparison.

**(2)** Is the total quantity purchased more than 100 but less than or equal to 500? If so, the total cost is ORDER QUANTITY multiplied by $6. If not, move on to the next comparison.

**(3) ** Is the total quantity purchased more than 500 but less than or equal to 1000? If so, the total cost is ORDER QUANTITY multiplied by $3. If not, move on to the next comparison.

**(4)** The total cost is ORDER QUANTITY multiplied by $1 since at this point the only logical conclusion is the customer purchased more than 1000 items.

Here is the **NESTED IF formula** to do all these four comparisons one after the other and give us the total price:

### =IF(B8<=B2,A2*B8,IF(B8<=B3,A3*B8,IF(B8<=B4,A4*B8,A5*B8)))

**NOTE**: In the above example, you should enter this formula into cell **B10**.

For every ORDER QUANTITY, this formula calculates the correct TOTAL COST.

Our estimating spreadsheet only does one quantity at a time. I’m trying to create a formula that will take the first quantity number and extrapolate

a discounted price based on multiple higher quantities.