Ask Your Question
0

How do I replace all spreadsheet values with their RANK? [closed]

asked 2014-07-17 16:03:05 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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&auth...

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-29 21:55:05.495296

Comments

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.

Lupp gravatar imageLupp ( 2014-07-17 18:24:34 +0100 )edit

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.

shawn.1671 gravatar imageshawn.1671 ( 2014-07-17 19:07:20 +0100 )edit
1

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".

Lupp gravatar imageLupp ( 2014-07-17 20:27:16 +0100 )edit

The former is appropriate. The problem is solved. You've helped me out a lot; thank you! I will be clear going forward.

shawn.1671 gravatar imageshawn.1671 ( 2014-07-17 21:44:51 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2014-07-17 19:31:52 +0100

Lupp gravatar image

updated 2014-07-17 22:36:44 +0100

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-07-17 16:03:05 +0100

Seen: 460 times

Last updated: Jul 17 '14