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?

 

[collegeitdept]

Can an Excel MVP user help please?

 

[bschwartz]

No one else can help?

 

[Harry Flashman]

Hello,

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

I think you need to provide more of an explanation of exactly how your data is laid out and what you are trying to achieve.

This is how I have interpreted "the aggregate of multiple columns across a sheet"

1. You have values laid out across multiple columns with one set of values per row (like a cross tab).
2. You want to sum each the values in each row (that is the sum of all the columns per row)
3. You want to RANK those summed values (for example using the RANK function)
4. To complicate things your data is spread across multiple spreadsheets (but otherwise is laid out in the same way - that is the same number of columns on each spreadsheet).

Thus in summary you want to somehow compile all the rows of data from each spreadsheet and sum the values of each row.

Here is an illustration of what I mean:

Thee worksheet: Sheet1, Sheet2, Sheet3

The data for each is laid out in a grid - A2:A6

For example:
Sheet1:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
</tbody>[/TABLE]

Sheet3

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]
[/TR]
</tbody>[/TABLE]


On Sheet 4 we could compile the summed values of each row of each worksheet: Thus laid out in a grid from A1:C6
[TABLE="width: 178"]
<tbody>[TR]
[TD]Sheet1[/TD]
[TD]Sheet2[/TD]
[TD]Sheet3[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]11[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]14[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]15[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]13[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Here is the formula which will do this:
=SUM(INDIRECT(A$1&"!"&""&ROW()&":"&ROW()&""))

We can now use a formula to rank those values (we'll make a little grid somewhere on Sheet4 to the right of our aggregated data)
=RANK(A2,$A$2:$C$6,0)
Which returns the following

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Sheet1[/TD]
[TD="width: 64"]Sheet2[/TD]
[TD="width: 64"]Sheet3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

Thus we can see the the row with the highest summed value was the third row of Sheet3 (actually row 4 because we could assume their was a header row on Sheet1 to Sheet3).

That is one possible interpretation of you mean by "the rank of the aggregate of multiple columns across sheets". Is that sort of what you are trying to do?
Otherwise maybe if you provided a little more information you will get some more responses.

At any rate I think this problem will be more easily solved if you use an intermediate step as I have above - that is the return the sum of the values you are interested in in one table, and then in another table rank the values in the first table.

 

[Harry Flashman]

In my example above, on Sheet4, where I have complied the data A1 to C1 contains the words Sheet1, Sheet2, Sheet 3. That is what is being reference with our INDIRECT formula.
(I thought I have better mention that in case I had not made that clear). The formula is entered into A2 and fills the range A2:C6.

 

[collegeitdept]

In my example above, on Sheet4, where I have complied the data A1 to C1 contains the words Sheet1, Sheet2, Sheet 3. That is what is being reference with our INDIRECT formula.
(I thought I have better mention that in case I had not made that clear). The formula is entered into A2 and fills the range A2:C6.

Hi Harry,

Thanks so much for replying... You are very close!

Actually in your example (and it's almost spot on) all worksheets are laid out roughly the same... (some has more columns than others)....

I want to sum all rows in all those worksheets in a particular column... like "sales".... so that is why I have an index match formula - one for the first row of a variable column than another index match for the last (most recent) row/bottom row of that same variable column. IE: index match row 1 ":" index match last row

than sum of all the sales columns from each worksheet.

than rank them.


Column A for each worksheet is the date

So for the most current date: 10/13/2013 sum up all "sales" numbers from all worksheets. Take that aggregated sum of sales for that date and get the rank for the all sales (aggregate of all sales columns in each worksheet).


The AGGREGATE function in Excel 2010 does this as well for average, min, max... but it can't explicitly do RANK.

Thanks.

 

[collegeitdept]

Also caveat.... I cannot insert a new column as an aggregate column.

 

[collegeitdept]

Also because I have to rank many variables.... I am using a lookup command (index match) so I can lookup sales (column headers) data between a certain date (column a)....

 

[Harry Flashman]

Hi Harry,

Thanks so much for replying... You are very close!

Actually in your example (and it's almost spot on) all worksheets are laid out roughly the same... (some has more columns than others)....

I want to sum all rows in all those worksheets in a particular column... like "sales".... so that is why I have an index match formula - one for the first row of a variable column than another index match for the last (most recent) row/bottom row of that same variable column. IE: index match row 1 ":" index match last row

than sum of all the sales columns from each worksheet.

than rank them.


Column A for each worksheet is the date

So for the most current date: 10/13/2013 sum up all "sales" numbers from all worksheets. Take that aggregated sum of sales for that date and get the rank for the all sales (aggregate of all sales columns in each worksheet).


The AGGREGATE function in Excel 2010 does this as well for average, min, max... but it can't explicitly do RANK.

Thanks.

I still think you should do this in two steps:

Use a formula consolidate/sum all the relevant data on a single spreadsheet. Because you want to sum the data conditionally I think the function you might want to try is SUMIF (or SUMIFS if there are multiple conditions).
Then in a separate table use the RANK function to rank the consolidated data.

You might want to consider using named ranges instead of normal references. It will make your formula's easier to read. Possibly even dynamic named ranges might be best. Create your named ranges with a consistent naming convention - but with little prefixes to indicate the worksheet each name applies to. It makes it easier to edit the formulas if you need to modify them slightly depending on what worksheet you are referencing.


Generally speaking when you are faced with a complicated problem it is best to break it down into simple steps. It is often the case that once you have achieved those intermediate steps, a way to join those steps will then present itself.

 

[collegeitdept]

I still think you should do this in two steps:

Use a formula consolidate/sum all the relevant data on a single spreadsheet. Because you want to sum the data conditionally I think the function you might want to try is SUMIF (or SUMIFS if there are multiple conditions).
Then in a separate table use the RANK function to rank the consolidated data.

You might want to consider using named ranges instead of normal references. It will make your formula's easier to read. Possibly even dynamic named ranges might be best.


Generally speaking when you are faced with a complicated problem it is best to break it down into simple steps. It is often the case that once you have achieved those intermediate steps, a way to join those steps will the present itself.

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