Ranking by multiple columns
-
ffxigotenks
- Posts: 4
- Joined: Fri Oct 30, 2009 6:39 pm
Ranking by multiple columns
I've been working on a spreadsheet and I want it to be able to rank in column C first by column A. then by column B, I'm not sure if/how I could nest the RANK command.
OpenOffice 2.4 on Ubuntu 8.10
-
ken johnson
- Volunteer
- Posts: 918
- Joined: Sun May 31, 2009 1:35 am
- Location: Sydney, Australia
Re: Ranking by multiple columns
If the column A scores occupy A1:A30 and the column B scores occupy B1:B30 then try...
=RANK(B1;IF(A1:A30=A1;B1:B30;""))
It's an array function so it has to be entered using the Ctrl+Shift+Enter key combination.
After Ctrl+Shift+Entering the formula into C1, copy it then select C2:C30 (adjust to suit your data) then paste.
Don't try using the fill handle, it doesn't work with array formulas and you could end up in a real mess.
The IF(A1:A30=A1;B1:B30;"") part of the formula constructs an array (internally) that can be read this way...
For each of the scores in column A, if it is equal to A1 then it has the same rank as A1, so the array element is made equal to the corresponding column B score. If it is not equal to A1 then it does not have the same rank as A1 so the array element is made equal to "". The final array that is used to rank the B1 score consists of all the column B scores that share the same rank as A1 in the column A scores. Column B scores with dissimilar column A ranks are excluded because they have been converted to "" in the array.
Ken Johnson
=RANK(B1;IF(A1:A30=A1;B1:B30;""))
It's an array function so it has to be entered using the Ctrl+Shift+Enter key combination.
After Ctrl+Shift+Entering the formula into C1, copy it then select C2:C30 (adjust to suit your data) then paste.
Don't try using the fill handle, it doesn't work with array formulas and you could end up in a real mess.
The IF(A1:A30=A1;B1:B30;"") part of the formula constructs an array (internally) that can be read this way...
For each of the scores in column A, if it is equal to A1 then it has the same rank as A1, so the array element is made equal to the corresponding column B score. If it is not equal to A1 then it does not have the same rank as A1 so the array element is made equal to "". The final array that is used to rank the B1 score consists of all the column B scores that share the same rank as A1 in the column A scores. Column B scores with dissimilar column A ranks are excluded because they have been converted to "" in the array.
Ken Johnson
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.