How to Use Excel NESTED IF Function

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

Consider the below situation:

How to Calculate Quantity Discount Price for Bulk Purchases with MS Excel NESTED IF Function 1
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.

How to Calculate Quantity Discount Price for Bulk Purchases with MS Excel NESTED IF Function 2

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.

MORE INFO

How to Aggregate MS Excel Daily Data with Pivot Table
How to Count the Number of Days in Excel with Running Averages
Use ISNA Function to Handle “Not Available” Excel Data
How to Use SUMIF Conditional SUM Function in Excel
How to Use Excel NESTED IF Function
How to Line Up Numbers on Decimal Points in Excel

1 Comment

  1. Rustin on November 27, 2015 at 12:19 pm

    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.