Microsoft Office Forums How can I calculate price discounts and also identify top customer?
 Register FAQ Search Today's Posts Mark Forums Read

#1
12-24-2014, 12:16 PM
 cwarfox Windows Vista Office 2007 Novice Join Date: Dec 2014 Posts: 1
How can I calculate price discounts and also identify top customer?

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
12-25-2014, 02:27 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,387

HI
Perhaps something like
Code:
`=A1*lookup(A1,{0,500,100,2000},{0,5%,10%,15%})`
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
#3
12-25-2014, 04:42 AM
 thetraininglady Windows 7 64bit Office 2010 32bit Competent Performer Join Date: Sep 2014 Location: Sydney, Australia Posts: 214

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.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post 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

All times are GMT -7. The time now is 12:21 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top