Rank across worksheet columns

[collegeitdept]

Hello,

Does anyone know of a way to get the rank of an aggregate of multiple columns across sheets in a workbook please?

I found a non-workable but good formula:
=SUMPRODUCT(--(A$2:A$10+B$2:B$10>A2+B2))+1

BUT! it doesn't allow for a lookup/search variable - the columns are "locked" it does not support an index match formula.

IE: rank current sales number Year to date = Index match first day of year : index match current date *** only do this across 3 worksheets.


Excel 2010 has an excellent new function called: aggregate... but it doesn't support rank!

Here I have the average of all sales year to date from 3 separate worksheets within the workbook:

=AGGREGATE(1,4,INDEX('E! Online US'!$A$12:$L10000,MATCH(DATE(YEAR($B$3),1,1),'E! Online US'!$A$12:$A10000,0),MATCH(G$2,'E! Online US'!$A$11:$L$11,0)):INDEX('E! Online US'!$A$12:$L10000,MATCH($B$3,'E! Online US'!$A$12:$A10000,0),MATCH(G$2,'E! Online US'!$A$11:$L$11,0)),INDEX('Eonline App'!$A$12:$L10000,MATCH(DATE(YEAR($B$3),1,1),'Eonline App'!$A$12:$A10000,0),MATCH(G$2,'Eonline App'!$A$11:$L$11,0)):INDEX('Eonline App'!$A$12:$L10000,MATCH($B$3,'Eonline App'!$A$12:$A10000,0),MATCH(G$2,'Eonline App'!$A$11:$L$11,0)),INDEX('EOL Mobile Web'!$A$12:$L10000,MATCH(DATE(YEAR($B$3),1,1),'EOL Mobile Web'!$A$12:$A10000,0),MATCH(G$2,'EOL Mobile Web'!$A$11:$L$11,0)):INDEX('EOL Mobile Web'!$A$12:$L10000,MATCH($B$3,'EOL Mobile Web'!$A$12:$A10000,0),MATCH(G$2,'EOL Mobile Web'!$A$11:$L$11,0)))



I need an 'aggregate' formula that can do rank.

Can someone please help?

 

[Harry Flashman]

Would there be away for it to be done without breaking it down into two steps please?

This is fairly complicated problem - is hard to say without actually seeing the data. You can send me a workbook if you want. Otherwise maybe if you paste some of the data into this forum you will get more help.

 

[collegeitdept]

I found a sumproduct function that provides the rank... and being that sumproduct can also do a lookup/condition as well maybe sumproduct can be used?

=SUMPRODUCT(--($A$2:$A$100=$A2),--(B$2:B$100>B2))+1


Thanks.

 

[Harry Flashman]

I found a sumproduct function that provides the rank... and being that sumproduct can also do a lookup/condition as well maybe sumproduct can be used?

=SUMPRODUCT(--($A$2:$A$100=$A2),--(B$2:B$100>B2))+1


Thanks.

How is one supposed to use that formula? What does it do.

I just tested by putting random numbers between 1 and 500 into A2:B100 and then entered that formula into C2 and dragged down. It filled each cell with either a 1 or 2. What exactly is it supposed to be ranking? What do the returned values even mean?

 

[collegeitdept]

Please find the file:

https://dl.dropboxusercontent.com/u/15717201/Excel Question.xlsx

 

[collegeitdept]

How is one supposed to use that formula? What does it do.

I just tested by putting random numbers between 1 and 500 into A2:B100 and then entered that formula into C2 and dragged down. It filled each cell with either a 1 or 2. What exactly is it supposed to be ranking? What do the returned values even mean?

Column A is the category/variable... Column B is the data.

So it can rank all Column B rows that has "Sales" in it's associated rows in column A.

 

[Harry Flashman]

Please find the file:

https://dl.dropboxusercontent.com/u/15717201/Excel Question.xlsx

Have you considered consolidating all of the data onto the same spreadsheet and then using a pivottable to analyse your data. A pivot table can aggregate and rank your data with ease.
To consolidate your data all you could insert an new column which shows the spreadsheet origin.

We can still look for a formula solution of course but a pivot table could solve a problem like the one you have shown in that workbook very quickly.


This is what your proposed summary table looks like:

Aggregate	Rank	Rank	Rank
Date	Sales	Units	Palettes
15/08/2013
22/08/2013
29/08/2013
5/09/2013
12/09/2013
19/09/2013
26/09/2013
3/10/2013
10/10/2013
17/10/2013
24/10/2013

<tbody>
</tbody>

However I have notice the sample data you have provided does not actually have any August dates:

Here is a sample of what a pivot table was able to return after I combined your spreadsheets:

Row Labels	Palettes	Sales	Units
7/09/2013	33	83	124
13/09/2013	17	44	80
14/09/2013	2	13	36
19/09/2013	16	22	60
20/09/2013	14	11	52
21/09/2013	18	30	70
24/09/2013	29	81	238
25/09/2013	86	155	416
26/09/2013	19	32	68
27/09/2013	80	122	230
28/09/2013	12	33	56
29/09/2013	24	12	19
30/09/2013	122	56	156
1/10/2013	32	55	113
2/10/2013	516	381	837
3/10/2013	37	59	79

<tbody>
</tbody>

etc (the data continues on until 24th October 2013)

That is sum but I can turn that into rank with the click of button:

Row Labels	Palettes	Sales	Units	Grand Total
7/09/2013	9	7	10	10
13/09/2013	17	13	12	13
14/09/2013	30	25	22	24
19/09/2013	18	21	16	18
20/09/2013	20	27	19	20
21/09/2013	16	18	14	15
24/09/2013	11	8	5	7
25/09/2013	5	5	2	2
26/09/2013	15	16	15	14
27/09/2013	6	6	6	6
28/09/2013	22	15	17	16
29/09/2013	12	26	28	23
30/09/2013	3	11	8	8
1/10/2013	10	12	11	11
2/10/2013	1	1	1	1
3/10/2013	8	10	13	12

<tbody>
</tbody>

Or I can sum by Platform (your worksheet name) and month

Worksheet	Date	Palettes	Sales	Units	Grand Total
Platform 1	Sep	295	392	1149	1836
	Oct	639	395	1011	2045
Platform 2	Sep	76	181	295	552
	Oct	190	348	536	1074
Platform 3	Sep	22	46	59	127
	Oct	89	257	309	655
Platform 4	Sep	3	4	21	28
	Oct	53	83	144	280
Platform 5	Sep	76	71	81	228
	Oct	198	284	308	790
Grand Total		1641	2061	3913	7615

<tbody>
</tbody>

I know this is not what you asked for but it a potential is solution in case an MVP can't help you (I am no where near as good as those guys). And at least it keeps your post visible - someone else might see it and offer a better solution.

 

[Harry Flashman]

Column A is the category/variable... Column B is the data.

So it can rank all Column B rows that has "Sales" in it's associated rows in column A.

Thank you. I had a feeling it might be something like that. Rank by segment.

That is a useful formula. Thank your for sharing. I have a formula that does a similar job. But it is an array formula. Yours is simpler.

=SUM(N(B2<=IF(A2=Segment,Values,-1E+40)))

(Segment and Values are named ranges).

 

[collegeitdept]

I can't do that... that's not even the spreadsheet I will be using. it's full of random data.

The actual corporate file is huge and I cannot modify because other users.



Again what's needed is to find the ranking of all "sales" columns from all worksheets.

Can any MVP help please?

 

[collegeitdept]

If there's an advanced Excel user that can help, please see my new thread here:

http://www.mrexcel.com/forum/excel-questions/734950-excel-mvp-needed-please-help-aggregate-rank.html