I’m using the latest Calc with Ubuntu 14.04. How can I replace all values with their RANK, given about 9238 values? Any feedback is appreciated; I’ll continue to look for a solution. The data can be found here: https://drive.google.com/?tab=mo&authuser=0#folders/0B3fa1Bco9n2EWk5vdTZiLWZRaUk
I don’t know for what reason the other thread you had started was closed. But it seems you didn’t study the answers - or there was a misunderstanding. Those who try to help you will, of course, need clear and sufficiently complete information. Otherwise they might waste their (and other people’s) time.
Given your new question it will hopefully not be necessary to develop a solution for 9238 values - and then another one for 9239 specimen. Please inform us of the relevant aspects of your problem
, an that in sufficient detail.
I cannot find a way to rank the entire dataset; the answers gave a partial solution. If you have a spreadsheet of values in, say, the first two columns, then how would you go about replacing those values with their rank in the data? The smallest value gets replaced by a 1 for every appearance, the next smallest gets a 2, etc.
An ambiguity: “… The smallest value gets replaced by a 1 for every appearance, the next smallest gets a 2, etc.” If you have 5 occurrences of the smallest value (the integer 7, e.g.) these shall all be assigned the rank 1. The next smallest value is 9 and occurs twice … RANK() will assign them both rank 6. Is this what you want? Or do you expect a 2 meaning “second smallest VALUE”.
The former is appropriate. The problem is solved. You’ve helped me out a lot; thank you! I will be clear going forward.
I already told you in my answer to the other question (closed / no longer available) that I CANNOT REPLACE numbers or something else “in situ” by standard means of Calc which is formulae.
Actually replacing them by their ranks will require
A) quite a bit of real programming, using the API (ask a “MACRO” question for that) or
B) preparing an array of resulting ranks by formulae and then copy that array and ‘Paste Special’ into the original space with ‘Numbers’ ONLY (thereby possibly destroying the usefulness of the formulae for a future application).
My explicit suggestion is: Add an extra range / sheet for the ranks and leave the original numbers in place.
I would also suggest to clearly decide in which way you may collect the occurring (or the admisive ?) values and whether they will be sorted or not. The problem becomes much easier if we don’t need to generate that collection by formulae and/or by interactive means.
See attached example, please. ask37135ComplicatedRanking001.ods