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.