Multiple Cumulative %'s

[Ed_Global]

I have a data set as the below simple example which is product code sales value for year

	Yr 1 Sales	Yr 2 Sales	Yr 2 Cum	Cumulative %
Prod A	35	50	50	36%
Prod B	55	40	90	64%
Prod C	0	30	120	86%
Prod D	25	20	140	100%
Total	115	140

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

It has been sorted to be largest to smallest sales for the second year. I then have a cumulative sales calculation field and the cumulative sales % column also (cumulative sales/total sales).

The cumulative % figure then drives a logic statement to classify the products (important to less important).

I want to also know the same info for year 1 but based on the largest product for year 1 to the smallest product for year 1. Is there a way to do this with rank or some other formula? My only way at present is to copy the dataset then resort it based on year 1 then do the cumulative sum and % calculations. But as I have 5 years sales I'll end up with 5 data sets then which is a bit messy. Any ideas?

Regards

Ed

 

[DRSteele]

I think this might help.

Copy the formulas in Row2 down to Row5, and the one in B6 across.

[Note that I'm not implying ColumnA has the correct product for the figures in Columns D through G.]

Book1
	A	B	C	D	E	F	G
1	Pruduct	Yr1 Sales	Yr2 Sales	Yr 1 Cum	Yr1 Cum%	Yr2 Cum	Yr2 Cum%
2	Prod A	35	50	55	48%	50	36%
3	Prod B	55	40	35	78%	40	64%
4	Prod C	0	30	25	100%	30	86%
5	Prod D	25	20	0	100%	20	100%
6	Total	115	140	115		140
Sheet58

Cell Formulas
Range	Formula
D2	=AGGREGATE(14,6,B$2:B$5,ROWS(D$2:D2))
E2	=SUM(D$2:D2)/D$6
F2	=AGGREGATE(14,6,C$2:C$5,ROWS(F$2:F2))
G2	=SUM(F$2:F2)/F$6
B6	=SUM(B2:B5)

 

[markmzz]

Hi!

Maybe the formulas below can helps.

In G2 and copy down - Array Formula - use Ctrl+Shift+Enter to enter the formula

=INDEX($A$2:$A$5,MATCH(LARGE(INDEX($B$2:$F$5,,CEILING(COLUMNS($G2:G2),2)/2)-ROW($A$2:$A$5)/10^9,ROWS(G$2:G2)),
INDEX($B$2:$F$5,,CEILING(COLUMNS($G2:G2),2)/2)-ROW(A$2:A$5)/10^9,0))

In H2 and copy down - Normal Formula - use only Enter to enter the formula

=SUMPRODUCT(LARGE(INDEX($B$2:$F$5,,CEILING(COLUMNS($H2:H2),2)/2),ROW(H$2:H2)-ROW(H$2)+1))/
INDEX($B$7:$F$7,CEILING(COLUMNS($H2:H2),2)/2)

After that, copy the range G2:H5 to the right until the column P.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Yr 1 Sales	Yr 2 Sales	Yr 3 Sales	Yr 4 Sales	Yr 5 Sales	Product	Yr 1 Sales	Product	Yr 2 Sales	Product	Yr 3 Sales	Product	Yr 4 Sales	Product	Yr 5 Sales
2	Prod A	35	50	77	8	40	Prod B	32%	Prod A	36%	Prod A	33%	Prod C	37%	Prod D	45%
3	Prod B	55	30	49	49	35	Prod C	65%	Prod C	64%	Prod D	57%	Prod D	69%	Prod A	69%
4	Prod C	55	40	51	67	17	Prod A	85%	Prod B	86%	Prod C	79%	Prod B	96%	Prod B	90%
5	Prod D	25	20	53	58	74	Prod D	100%	Prod D	100%	Prod B	100%	Prod A	100%	Prod C	100%
6
7	Total	170	140	230	182	166
8
***	*******	*********	*********	*********	*********	*********	********	*********	********	*********	********	*********	********	*********	********	*********	**

<tbody>
</tbody>

I hope that this helps.

Markmzz

 

[markmzz]

Hi again!

If you want only the % (no matter the order of the products), then try the formula below:

In G2 and copy down and to the right

=SUMPRODUCT(LARGE(B$2:B$5,ROW(G$2:G2)-ROW(G$2)+1))/B$7

I hope that this helps too.

Markmzz