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:
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.
MORE INFO
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.