IF statements allow you to change a spreadsheet cell’s result depending on whether something is true or false. A good example is when you want to calculate how much discount to give a customer depending on how many items they have bought. e.g. If my customer is buying more than ten of a particular product I will give them a 10% discount, if not, then there’s no discount.
You can follow the tutorial below which will explain how to build the example or you can save time and purchase the Nested IF Statement Spreadsheet Example that we have prepared already.
Purchase the Nested IF Statement Spreadsheet Example
Click the button below to purchase the Nested IF Statement Spreadsheet Example
On completing your purchase in PayPal you receive the download link via email.
Following the Nested IF Statement tutorial
So, we want to offer our customer differing levels of discount depending on the quantity of products they buy.
e.g. If my customer is buying more than ten of a particular product I will give them a 10% discount, if not, then there’s no discount.
Here’s another way of saying the same thing:
If Quantity >=10 then Discount = 10% otherwise Discount = 0%
We can add a formula to our spreadsheet which will automatically calculate the discount for us. Here is a sales order line where the customer is buying 10 spades from us, at £3 per spade, giving a total of £30.
But, we give a discount of ten percent if they are buying 10 or more. We enter an IF statement into cell F6 which checks the value in D6 (quantity) and gives us a different result depending on whether it is ten or more.
=IF(D6>=10,E6*0.1,0)
In plain English this reads as:
If D6 (the quantity) is greater than or equal to 10, then multiply E6 (Pre Discount Price) by 0.1 (equivalent to 10%). If D6 is not greater or equal to 10 then just put a zero.
For our example the quantity is 10 so our customer qualifies for the discount and a discount of £3.00 is calculated, being 10% of £30.00.
We have just done an IF statement
What if we have a second level of discount?
Perhaps we reward our customers with a 20% discount if they buy 50 or more of an item. We could add a new cell with a completely new IF statement which checks the quantity to see if it is 50 or over, but this would mess up our nice layout and would probably get a bit confusing. Luckily we can take our initial IF statement and work on it a bit more to make a Nested IF statement.
We want the computer to check the higher value first, so we say:
If Quantity >=50 then Discount = 20% otherwise If Quantity >=10 then Discount = 10% otherwise Discount = 0%
Here’s how to type that in Excel-speak:
=IF(C14>=50,D14*0.2,IF(C14>=10,D14*0.1,0))
So we can see that when the customer orders 50 hoes they qualify for the 20% discount and our Nested IF statement takes that into consideration. The discount is 20% of £200 which is £40 giving a line total of £200 – £40 = £160.
The final order the customer makes is for two planters so they don’t qualify for a discount there.
You can keep adding further discount levels in the same way. Although, if you have a lot of discount levels it would probably be a better idea to use a database to do your sales order processing. Updating your discount levels and values would be a lot easier then as you wouldn’t have to remember to edit each spreadsheet. It could also automate keeping track of late payments.
If you would like to save some time you can purchase the Nested IF Statement Spreadsheet Example by clicking the button below.
After making the purchase in PayPal you receive a download link by email.
