Let’s assume we run a business in which we offer customers a discounted price based on quantities purchased. We can afford to offer these discounts because our inventory costs also decrease when we purchase in bulk from our wholesalers. By passing on the savings to our retail customers we are able to drive higher sales and production.
- Our non-discounted retail price is $25 per product.
- However, if the customer purchases 100 or more units in an order, the discount off the retail price of $25 is 10%.
- To provide an incentive to our customers to buy in even greater quantities, we discount the price by 25% for all orders of 500 or more.
We need a solution that calculates the final price of our customers’ orders while considering any discounts due them based on their order quantity.
To simplify this example a bit, we will first break down our eventual formula by 3 different logical tests.
Test 1: If ‘Purchase Qty’ is greater than 100
Test 2: If ‘Purchase Qty’ is less than 500
Test 3: If ‘Purchase Qty’ is greater than 500
We then build separate ‘IF’ functions for each in order to return either a ‘TRUE’ or ‘FALSE’ based on the ‘Purchase Qty’ on each row of data.
We will then plug in the cell references that contain these results into our larger formula for the sake of simplicity.
For example, the formula and result for Test 1 on row 10 is illustrated in the next figure.
Cell ‘B5’ refers to the minimum quantity the customer must purchase in order to qualify for a 10% discount, or 100.
Test 2 will refer to the upper maximum for quantities qualifying for 10% and then Test 2 will refer to the minimum quantity to qualify for the 25% discount.
Notice that we simply use ‘TRUE’ and ‘FALSE’ as our ‘value_if_true’ and ‘value_if_false’ arguments for the sake of simplicity.
All ‘TRUE’ values in the test columns are bolded for the sake of emphasis.
Each cell in our test columns evaluate to either a ‘TRUE’ or ‘FALSE’ value making it easier for us to validate the results of our eventual nested formula.
All of our logical test formulas get us the following results. You can test each one based on the definition of the three tests above and see that the true and false values on each row correlate.
These results can now be inserted into our larger formula combining ‘IF’ with ‘AND’ as well as inserting a second ‘IF’ function as the ‘value_if_false’ argument of the initial ‘IF’ function.
Specifically, the first ‘IF’ combined with the ‘AND’ tests for the results of Test 1 and Test 2, and the ‘value_if_true’ (remember, in this case that both Test 1 and Test 2 must evaluate to ‘TRUE’) is ‘D5’ which is $22.50 (the discounted price for orders of 100 or more but less than 500).
The ‘value_if_false’ is actually another ‘IF’ function that evaluates or Test 3 result. If this result is ‘TRUE’, the function returns the value in ‘D6’ which is $18.75 (the discounted price for orders of 500 or more). If this ‘IF’ function evaluates to ‘FALSE’ it will return the value in ‘D2’, or $25.00.
Putting all this together, the formulas and results are shown in the following figure.