How can I calculate price discounts and also identify top customer?

[cwarfox]

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

[Pecoflyer]

HI
Perhaps something like

=A1*lookup(A1,{0,500,100,2000},{0,5%,10%,15%})

[thetraininglady]

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.