Multiple Cumulative %'s

[Ed_Global]

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

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Yr 1 Sales[/TD]
[TD="width: 64"]Yr 2 Sales[/TD]
[TD="width: 64"]Yr 2 Cum[/TD]
[TD="width: 64"]Cumulative %[/TD]
[/TR]
[TR]
[TD]Prod A[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="class: xl63, align: right"]36%[/TD]
[/TR]
[TR]
[TD]Prod B[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]90[/TD]
[TD="class: xl63, align: right"]64%[/TD]
[/TR]
[TR]
[TD]Prod C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]120[/TD]
[TD="class: xl63, align: right"]86%[/TD]
[/TR]
[TR]
[TD]Prod D[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]140[/TD]
[TD="class: xl63, align: right"]100%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]140[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.


[TABLE="class: grid, width: 1120"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Yr 1 Sales[/TD]
[TD="align: center"]Yr 2 Sales[/TD]
[TD="align: center"]Yr 3 Sales[/TD]
[TD="align: center"]Yr 4 Sales[/TD]
[TD="align: center"]Yr 5 Sales[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Yr 1 Sales[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Yr 2 Sales[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Yr 3 Sales[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Yr 4 Sales[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Yr 5 Sales[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Prod A[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]Prod B[/TD]
[TD="align: center"]32%[/TD]
[TD="align: center"]Prod A[/TD]
[TD="align: center"]36%[/TD]
[TD="align: center"]Prod A[/TD]
[TD="align: center"]33%[/TD]
[TD="align: center"]Prod C[/TD]
[TD="align: center"]37%[/TD]
[TD="align: center"]Prod D[/TD]
[TD="align: center"]45%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Prod B[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]Prod C[/TD]
[TD="align: center"]65%[/TD]
[TD="align: center"]Prod C[/TD]
[TD="align: center"]64%[/TD]
[TD="align: center"]Prod D[/TD]
[TD="align: center"]57%[/TD]
[TD="align: center"]Prod D[/TD]
[TD="align: center"]69%[/TD]
[TD="align: center"]Prod A[/TD]
[TD="align: center"]69%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Prod C[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]Prod A[/TD]
[TD="align: center"]85%[/TD]
[TD="align: center"]Prod B[/TD]
[TD="align: center"]86%[/TD]
[TD="align: center"]Prod C[/TD]
[TD="align: center"]79%[/TD]
[TD="align: center"]Prod B[/TD]
[TD="align: center"]96%[/TD]
[TD="align: center"]Prod B[/TD]
[TD="align: center"]90%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Prod D[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]Prod D[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]Prod D[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]Prod B[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]Prod A[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"]Prod C[/TD]
[TD="align: center"]100%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]170[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]182[/TD]
[TD="align: center"]166[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]*********[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


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