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

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-24-2014, 12:16 PM
cwarfox cwarfox is offline How can I calculate price discounts and also identify top customer? Windows Vista How can I calculate price discounts and also identify top customer? Office 2007
Novice
How can I calculate price discounts and also identify top customer?
 
Join Date: Dec 2014
Posts: 1
cwarfox is on a distinguished road
Question 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
Reply With Quote
  #2  
Old 12-25-2014, 02:27 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How can I calculate price discounts and also identify top customer? Windows 7 64bit How can I calculate price discounts and also identify top customer? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

HI
Perhaps something like
Code:
=A1*lookup(A1,{0,500,100,2000},{0,5%,10%,15%})
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #3  
Old 12-25-2014, 04:42 AM
thetraininglady thetraininglady is offline How can I calculate price discounts and also identify top customer? Windows 7 64bit How can I calculate price discounts and also identify top customer? Office 2010 32bit
Competent Performer
 
Join Date: Sep 2014
Location: Sydney, Australia
Posts: 214
thetraininglady is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
functions, help please

Thread Tools
Display Modes


Similar Threads
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
How can I calculate price discounts and also identify top customer? Excel: Item and Price wchristner Excel 2 07-21-2014 06:23 AM
How can I calculate price discounts and also identify top customer? Yearly price escalator? markg2 Excel 1 04-15-2012 01:47 AM
How can I calculate price discounts and also identify top customer? Customer questionnaire Dean PowerPoint 5 05-04-2011 04:40 AM
How can I calculate price discounts and also identify top customer? Customer selection in Word Jackissimus Word 2 02-27-2011 12:56 PM


All times are GMT -7. The time now is 08:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft