Solved

Excel 2007 - finding mid date in a distribution

Posted on 2011-03-22
7
262 Views
Last Modified: 2012-08-14
I have a spreadsheet of product sales.
This has months as column headers and products as the first row.
The body of data is the sales of each product per month.

I could do with sorting these products by the date when half the sales to-date was reached.

How can this be achieved?
0
Comment
Question by:Beamson
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 35189639
How do you define the sales-to-date limit ?
is it placed somewhere in a cell (thus dynamic) or is it a static value ?
0
 
LVL 1

Author Comment

by:Beamson
ID: 35189789
Sales to date are the total number of sales across all dates for which there are columns.

See attached file for a basic example.

(In my true data there are thousands of products sold with a distribution of sales over a period of 20 years.)
Example.xlsx
0
 
LVL 19

Expert Comment

by:akoster
ID: 35191766
if you select the data rows (row 2 and below),
on the Data tab, in the Sort & Filter group, click Sort.
select "Month in which midpoint occurs" in Sort By
select "Values" in Sort On
Select "old to new" or "New to Old" in order
click OK

as described in

http://office.microsoft.com/en-us/excel-help/sort-data-in-a-range-or-table-HP010073947.aspx#BMsort_by_a_custom_list
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 1

Author Comment

by:Beamson
ID: 35191881
Finding how to calculate "month in which midpoint occurs" is the key point of my question.  Once there, sorting that column in order is relatively easy.

I can find mean sales, median sales, percentiles and so on for each row but finding the date of the halfway point for each products sales is beyond me.
0
 
LVL 19

Accepted Solution

by:
akoster earned 250 total points
ID: 35192172
you could use matrix functions :

select cell T2
fill in formula, do not use [Enter] but instead [Ctrl]-[Shift]-[Enter]
=SUM(B$1:P$1*B2:P2) / Q2

Open in new window


format the result as a date and it will give you these dates :

September 16, 2010
July 6, 2010
April 16, 2010
March 14, 2010

0
 
LVL 19

Expert Comment

by:akoster
ID: 35192189
please note that this way, you calculate the mathematical average of the sales data. Eg. in the first column this average states that the half-sales point is mid september, while no products have been sold during september.
0
 
LVL 1

Author Closing Comment

by:Beamson
ID: 35193290
I thought this was going to take forever but you solved it quickly with such an elegantly simple formula.  Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select only certain columns not entire sheet 12 28
conditional formatting 4 42
Extract Unique List when in Two Columns in Excel 20 27
Excel VBA 4 26
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question