Excel 2007 - finding mid date in a distribution
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?
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?
ASKER
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
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
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
I thought this was going to take forever but you solved it quickly with such an elegantly simple formula. Thanks.
is it placed somewhere in a cell (thus dynamic) or is it a static value ?