Graduated Discounts – A Nested IF Statement Example in Excel 2013

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.

Sales Order line before calculating discount

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.

simple discount applied to sales line
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))

multiple levels of discount in excel nested if statement
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.
missing out on discount

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.

Mileage Claim Tool version 2.0

If you claim for your mileage you will like our Mileage Claim Tool, now updated to version 2.0. It’s in Excel 2013 and we’ll walk you through how to use it, it’s really simple.

Purchase the Mileage Claim Tool

Click the button below to purchase the Mileage Claim Tool.

Upon completing your purchase via PayPal you receive the download link by email.

Entering your mileage allowance

When you first open the Mileage Claim Tool it will look like this. ..read more

What is your time worth?

You never get time back…

Missing out isn’t fun.  Whether it is spending more time with family or friends, or having more time to work on growing your business, there never seems to be enough hours in the day. It can be easy to get dragged along by the latest distraction.  Suddenly you wonder where the day has gone and there’s still so much to do.  It can seem that this is the only way to be.  Isn’t everyone else behaving the same way?  Well, not everyone… ..read more

Adding and Removing VAT with VAT Calculator v3.0

This is our most up to date VAT Calculator in Excel 2013.  It automatically adds and removes VAT as well as giving totals.  Here’s how to use it.  It’s really simple!

First things first, download this zip file which contains the VAT Calculator which is an Excel 2013 file called VatCalculator3.xlsx.  It’s completely complementary Clarence. ..read more

Keep Your Customers Happy To Grow Your Business

They say it is easier to sell to an existing customer than to attract a new customer.  Studies have shown that the cost per sale is markedly smaller when selling again to people who are already familiar with you and your products.

Cross-selling and Up-Selling to existing customers is a proven way to increase your profitability as described in this article from the people at Marketing Donut. You already have a relationship with an existing customer.  You know which products they have already purchased from you and you can therefore suggest other products that could be useful to them.  Sometimes they might want a certain product but didn’t know that you supplied it.  As they already know you and your brand, they are more likely to stick with you and buy from you again.

You look after your customer and get the sale, and they get the items they want from a supplier they trust.  It’s one of those win-win situations people crave. ..read more

How To use your Sales Data to drive Targeted Marketing

Capture and analyse sales data

To turn sales data into more meaningful information for your business, make it tell you where the customer came from.  You can do this with promotional codes, for instance by labelling each of your different ad placements with a separate code.  Keep track of which ‘promo’ code a customer uses when making a purchase and you will build up a picture of which ads work, which products each customer likes, and which ads work for which customers for which products!  That’s targeted marketing. (Click diagram below for close-up). ..read more

6 Questions to ask yourself before buying Business Software

1. What do I need and want the software to do?

It may seem an obvious question!  I would really recommend writing it all down though.  It will really clarify what your requirements and expectations are.  Getting into the detail early will help you prepare and increase the likelihood of success.  When you contact potential suppliers or prospective developers it gives you a great starting point.

Once you have made your full list of requirements, separate them into ‘must-haves’ and ‘nice-to-haves’.  There will be some requirements which are absolutely crucial.  The sort of thing where you think, if it doesn’t do that there there’s no point. ..read more

Spreadsheets vs Database – Round 10: Ability to Adapt & Grow

In the final round we look at which tool is the better long term platform for your business as it adapts and grows.

As your business develops and grows, and your market evolves and matures, you want your business systems to have the scope and flexibility to develop and grow with you.  How is this best achieved whilst ensuring that bureaucracy bloat is kept to a minimum?

Round 10: Ability to Adapt & Grow

spreadsheets-do-not-adapt-and-growAs a business grows and becomes more complex its systems need to help keep you on top of this.

You may need to develop more systems and processes.  These will need controls and measures in place.  Staff training and monitoring becomes more important.

If you were to base a growing business on a spreadsheet system you would ..read more

Spreadsheets vs Database – Round 9: Monitor Team Performance

In this penultimate round we look at whether spreadsheets or database are a more effective tool when you want to monitor and review your team’s performance.  How do each of these bits of software help you keep on track with who is doing what, their progress, who is reaching new heights and who needs some help.

Round 9: Monitoring Team Performance

A graphic illustrating monitoring team performance with spreadsheetsHow would you use a spreadsheet to monitor and review your team’s performance?

You could keep track of certain key measurements, such as sales, in a list and then compile these into a graph showing changes over time. ..read more