# rank cell value by column values in google sheets formula

So I have a table of apartments I'm looking at, and bunch of parameters I consider for each apartment, such as Price, size,distance etc..

Heres an example:

So the conditional formatting helps me color up values automatically so I can see what value is like according to the rest of the values in that column..

For example you can see that price 3000 is high because its red as its the maximum price around..

Now I want to put that in numbers, so for example this price 3000 will get a low rank of 1 out of 5 and I will do so for each parameter and eventually sum up all the ranks to get the total rank of the apartment.. (perhaps I'll give a different weight for each rank)

So I have thought.. how can I give a cell value ranking generally..

So In mathematics I would say that there should be transformation matrix from 1 axis which on price its [2600:3000] to the ranking axis which is [1:5].

something like this:

(don't mind the opposite rank.. I can always do `5 - {rank}` to get the rank in reverse..)

But I'm not sure how to do that..

we can easily get values such as `MINIMUM(B:B)` and `MAXIMUM(B:B)` and `AVERAGE(B:B)`

But I haven't figured out how to combine those to reach my goal..

The solution of course must be generic.. for any kind of values (when more apartments will come, and also for multiple columns as you can see, the values are different..

So in general for vector X it will be:

Any help or thoughts would be highly appreciated! thanks!

• hmm wow this looks very good!! I'll play with it more to see if I can get a solid results out of it – Yarden Cohen Jun 9 '18 at 8:49