Open Office Calc (Ersatz Excel) formula

[DaveC426913]

TL;DR Summary

I want a column in my score chart that displays ... "standings"

I want a column that displays what standing each team has. (I can't really Google a solution because I don't even know what to call it.)

This the idea:
- team 1 is "1st",
- teams 3 and 10 are "2nd",
- team 5 is 3rd,
etc.:

I can't think of a way of doing it that isn't very complex, involving some sort of text array ("1st", "2nd", etc.) where the top one gets knocked off each time it's used.

A simple sort descending won't work since there's duplicates (3 and 10 are tied for 2nd).
Also, I'd like to make it future-proof - meaning I can't count on the number of teams always being 10.

Maybe I can simplify it by only calculating up to 4th, but Still...

 

[hutchphd]

I must be misunderstanding. Why can't you just sort the array according to column AI ?

 

[DaveC426913]

I must be misunderstanding. Why can't you just sort the array according to column AI ?

 

[DaveC426913]

That ... was not what I was expecting.

I can't think of any excuses that don't sound stupid when they emerge from my face.

 

[PeterDonis]

I want a column that displays what standing each team has.

It looks like there's a statistical function, "rank", that does this.

 

[DaveC426913]

It looks like there's a statistical function, "rank", that does this.

That's exactly what I needed. I never occurred to me it might be built in.

But I think I may go with hutch's solution. It's actually the righter thing to do.

 

[anorlunda]

That ... was not what I was expecting.

I can't think of any excuses that don't sound stupid when they emerge from my face.

Don't feel bad. It happens to all of us from time to time.

 

[PeterDonis]

I think I may go with hutch's solution. It's actually the righter thing to do.

If all you want is to have the data sorted by rank, yes.

If you actually want the numeric rank explicitly, just sorting the array doesn't tell you that by itself. You still have to detect ties.

 

[DaveC426913]

If all you want is to have the data sorted by rank, yes.

If you actually want the numeric rank explicitly, just sorting the array doesn't tell you that by itself. You still have to detect ties.

Yeah, I'm OK with implicit rank. It's pretty intuitive to readers. People are used to scanning a list of ranking still they find their own.

I was just stuck with the presupposition of keeping the teams in their original order, blinding me to more elegant visualizations.