![]() |
#1
|
|||
|
|||
![]() There are three levels of discount: 5% when customer sales reach £500, 10% when customer sales reach £1000 and 15% when customer sales reach £2000. What's the formula to automatically calculate their discount? I also need to identify the Top Customer. Thanks |
#2
|
||||
|
||||
![]()
HI
Perhaps something like Code:
=A1*lookup(A1,{0,500,100,2000},{0,5%,10%,15%})
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
![]()
Depending on the way your data is set out I would probably use either HLOOKUP or VLOOKUP or you could even use an IF statement. The Lookup options mean you have your discount rates organised in a table so if you need to ever change these values E.g. say you change your lowest discount rate to 8% you just have to change this value in the table instead of manually going through your formulas and changing it in the individual formulas (or using Replace feature). You can also play around with different scenarios this way. Having said that I have a similar scenario exercise I use in my training courses for Excel and use some basic formula arguments (*/+-) to calculate volume discounts based on sales amount. It really comes down to how your data is set out.
To identify the top customers you could possibly use the Conditional Formatting rules to easily identify the top customer depending on which criteria you are going to use. Conditional formatting has Top/Bottom rules to identify just this exact thing. If you need some step by step guidance on conditional formatting you can check out this blog post: http://www.thetraininglady.com/apply...ting-in-excel/ just use the Top/Bottom Rules option instead. |
![]() |
Tags |
functions, help please |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need a Macro to go to column named price and ADD 16% to each field | okief1122 | Excel Programming | 4 | 09-06-2014 10:59 PM |
![]() |
wchristner | Excel | 2 | 07-21-2014 06:23 AM |
![]() |
markg2 | Excel | 1 | 04-15-2012 01:47 AM |
![]() |
Dean | PowerPoint | 5 | 05-04-2011 04:40 AM |
![]() |
Jackissimus | Word | 2 | 02-27-2011 12:56 PM |