Volume Discounts – Nested IF Statements

Do you have more than one discount level depending on volume or quantity?

The Simple Excel Volume Discount Tool can help you automatically calculate up to three different volume discount levels.

The Simple Excel Volume Discount Tool

  • Up to three volume discount levels
  • Auto-calculates discount per line
  • Sub-Total, VAT amount, Grand Total
  • Protected formulas so you can’t break it
  • Save As and re-use forever!

Screenshot of volume discount spreadsheet

 

 


 

Want to add a volume discount calculation to your own spreadsheet?

If you have more than one level of discount, you can use a Nested IF Statement.

In this example, there are two levels of discount.  If the customer orders more than 5, it’s a 10% discount.  If they order more than 10, it’s a 20% discount.

In cell C1 type:

=IF(B2>=10,A2*0.2,IF(B2>=5,A2*0.1,0))
Change the quantity to something over 10 and the formula will apply a 20% discount.
To finish, subtract the discount from the starting price.

 

 


 

Want some more tips and examples of how to use Excel to make your work easier?

Subscribe to our Newsletter for examples of Excel Spreadsheets, Business Admin Tips and Databases.