Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2019, 07:32 PM
Hoss Hoss is offline Calculate gain from improving worst performing decile to median level Windows 10 Calculate gain from improving worst performing decile to median level Office 2019
Novice
Calculate gain from improving worst performing decile to median level
 
Join Date: Feb 2019
Posts: 3
Hoss is on a distinguished road
Smile Calculate gain from improving worst performing decile to median level

I have a dataset (1,780 rows) and I'm trying to calculate the financial impact if bottom decile performed to median level.

I have product ID, qty_half price sales, qty_full price sales, $_half price sales, $_full price sales.



Any help greatly appreciated.

Thanks!
Reply With Quote
  #2  
Old 02-10-2019, 04:10 AM
Hoss Hoss is offline Calculate gain from improving worst performing decile to median level Windows 10 Calculate gain from improving worst performing decile to median level Office 2019
Novice
Calculate gain from improving worst performing decile to median level
 
Join Date: Feb 2019
Posts: 3
Hoss is on a distinguished road
Default

To follow on, I only need how you would approach this problem?

I have calculated the mean and identified the bottom decile. I’m now stuck on what I should do next.

Thanks to anyone that can help.
Reply With Quote
  #3  
Old 02-10-2019, 06:06 AM
Marcia's Avatar
Marcia Marcia is offline Calculate gain from improving worst performing decile to median level Windows 7 32bit Calculate gain from improving worst performing decile to median level Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

It would be best if a sample is attached with say, at least 30 rows of data. My understanding of your problem anyway is on how you will substitute or replace the quantity of the lowest decile with the mean so how about copying the sheet containing the actual data to another sheet then enter a formula in the lowest decile that picks up the mean in the first sheet? If you are making a one time projection of $sales based on the mean, a hard coded formula will do but if you want results where the decile dynamically change with any change in the actual sales, you must provide a data with expected results as I am sure we have several experts here who will happily share their own solutions or point to you the better approach to solving your problem.
Reply With Quote
  #4  
Old 02-10-2019, 12:09 PM
Hoss Hoss is offline Calculate gain from improving worst performing decile to median level Windows 10 Calculate gain from improving worst performing decile to median level Office 2019
Novice
Calculate gain from improving worst performing decile to median level
 
Join Date: Feb 2019
Posts: 3
Hoss is on a distinguished road
Default

Thanks Marcia! Attached is some sample data.

It shows number of products sold on "Sales" price.
Number of products sold at "Full" price.

I would like a method to calculate the financial return if lowest performing products (e.g. those sold at Sale price) sold at Full price.
Attached Files
File Type: xlsx Data.xlsx (10.5 KB, 10 views)
Reply With Quote
  #5  
Old 02-11-2019, 12:48 AM
ArviLaanemets ArviLaanemets is offline Calculate gain from improving worst performing decile to median level Windows 8 Calculate gain from improving worst performing decile to median level Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Does this do it?
Attached Files
File Type: xlsx SalesGain.xlsx (11.6 KB, 10 views)
Reply With Quote
  #6  
Old 02-11-2019, 04:43 AM
Marcia's Avatar
Marcia Marcia is offline Calculate gain from improving worst performing decile to median level Windows 7 32bit Calculate gain from improving worst performing decile to median level Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
I would like a method to calculate the financial return if lowest performing products (e.g. those sold at Sale price) sold at Full price.
This is different from your first post in which you were asking for a method to calculate the gain if the lowest 10 performing products are sold at the mean price. Arvi's post answered your second post while I tried a roundabout solution for post #1. I hope it gives you an idea on how you finally get the exact answers that you need.
Attached Files
File Type: xlsx Data.1.xlsx (16.7 KB, 7 views)
Reply With Quote
  #7  
Old 02-11-2019, 06:34 AM
p45cal's Avatar
p45cal p45cal is offline Calculate gain from improving worst performing decile to median level Windows 10 Calculate gain from improving worst performing decile to median level Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

How are you measuring perfomance?
If you're using column F for performance, the bottom decile can be calculated using
=PERCENTILE.EXC($F$2:$F$32,0.1)
or
=PERCENTILE.INC($F$2:$F$32,0.1)
The number of values below this value will be 3 or 4 products depending on which version (INC or EXC) you've used.
For those values falling in the bottom decile I'm not clear what values (column) you might want to substitute and how your median level (of what?) is calculated.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate gain from improving worst performing decile to median level Used an Array to Add a Median to Pivot Table- Now need Median to also appear on graph jrnisaac1 Excel 3 04-25-2017 08:45 AM
Calculate gain from improving worst performing decile to median level Suggestions wanted for improving workflow of shared incoming emails plaidma1 Outlook 1 05-03-2013 09:33 AM
Calculate gain from improving worst performing decile to median level Improving saving in Word solaster Word 2 07-13-2012 03:09 PM
Excel Not Finding the Median BrazzellMarketing Excel 4 02-17-2012 02:20 PM
Calculate gain from improving worst performing decile to median level Can I move Office Setup files to gain space KarenLeePA Office 1 06-24-2011 09:26 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:06 AM.


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