OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved] (workaround) RANK function help

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
MayaTekla
Newbie
Newbie


Joined: 20 Apr 2012
Posts: 3

PostPosted: Fri Apr 20, 2012 7:46 am    Post subject: [solved] (workaround) RANK function help Reply with quote

Hi All,

I have a set of data that I am attempting to rank, however the rank function causes there to be gaps in the rank numbering so for example if i have a data set:
1 3 5 7 7 10
They get ranked 1 2 2 4 5 6 (missing out the 3 and skipping onto 4!) Can anybody help me to create a formula or help write a function to do this?

I have started writing a function that does this for me in OO Basic, however I cant seem to get it to work (at least its almost there but I Just cant get the results I want)


Last edited by MayaTekla on Wed May 02, 2012 7:31 am; edited 1 time in total
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 397
Location: victoria

PostPosted: Fri Apr 20, 2012 8:34 am    Post subject: Reply with quote

Your numbers in A1 to F1-
Code:
=RANK(A1;$A$1:$F$1)+COUNTIF($A$1:A1;A1)-1

_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
MayaTekla
Newbie
Newbie


Joined: 20 Apr 2012
Posts: 3

PostPosted: Fri Apr 20, 2012 2:44 pm    Post subject: Reply with quote

Thanks Ozzie, however this still isnt quite right

If I have a data set such as:


Code:
Something  Rank
1             6
2             5
4             4
4             4
5             2
6             1

essentially this has changed it from two 3's to two 4's, Ideally Im after:

Code:
Something  Rank
1             5
2             4
4             3
4             3
5             2
6             1
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri Apr 20, 2012 6:33 pm    Post subject: Reply with quote

If the RANK function's second parameter is changed from the full list of 'Something' to a list of 'Something's Unique Values Only' you will get the rank value you are after...

Code:
Something  Uniques  Rank
1          1        5
2          2        4
4          4        3
4                   3
5          5        2
6          6        1
The second 4 is not in the list of Uniques
because it occurs earlier in the Something list


If 'Something' values are in A2:A7 the list of Uniques can be generated using...
Code:
=IF(COUNTIF(A$2:A2;A2)=1;A2;"")
filled down as far as required.
If the Unique values are in B2:B7, the desired ranks can be generated using
Code:
=RANK(A2;B$2:B$7)
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
MayaTekla
Newbie
Newbie


Joined: 20 Apr 2012
Posts: 3

PostPosted: Wed May 02, 2012 7:30 am    Post subject: Reply with quote

Thanks John, that looks like it will do the job, can just hide the extra column.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed May 02, 2012 8:09 pm    Post subject: Reply with quote

It can also be done without the extra column but then you have to use this complex array formula...
Code:
IF(A2="";"";RANK(A2;IF($A$2:$A$7="";"";IF(MATCH($A$2:$A$7;$A$2:$A$7;0)=ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1;$A$2:$A$7;""))))
where I have assumed that the list of values to be ranked occupy A2:A7. (Changing $A$2:$A$7 in the formula to a larger range to cater for a changing number of values to be ranked is OK. Using too large a range in an array formula is not a good idea though.)
Array formulae must be entered using Ctrl+Shift+Enter and when using the fill handle to fill the array formula into adjacent cells the Ctrl key must be held down while the fill handle is being dragged.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group