Firstly, humble apologies for not getting back on this. At the time I was very busy but saw the thread last night so I quickly wrote out a guide, see below:
The image below is showing a spreadsheet I did last week that automatically sorts the
championship points.
In the A column I've equalled the names of the drivers in the list. This example is for
the 1998 CART season. In column B I've equalled each drivers individual totals from
another sheet. Columns C, D, E, F and G are where the action is which follows in the next
step. All the formulas mentioned should work in OpenOffice as well.
1. We'll use column C to rank the points positions. A problem with the ranking function
is that equal values tend to throw it's calculation a bit (at least in excel), so a trick
I picked up off the net is to rank it but then also allow for equal numbers to be ranked
differently. Also remember it's important to "lock" the cells in the formula so that you
can just copy the formula down and not have to retype each one. In excel this is done
with the $ sign, not sure if OpenOffice does the same.
So to rank the numbers from B3 to B32, this is the formula you would use in cell C3
"=RANK(B3,$B$3:$B$32,0)+COUNTIF(B3:$B$32,B3)-1".
By "locking" the values B3 ($B$3),etc you can now just fill down or copy and paste this
formula in each row in column C and it will give you the ranking.
2. Now jump to column E (skip D for now), and fill in the position numbers in each row.
This is manually done, i.e. not a formula, just 1, 2, 3, etc. to the end of your number
of people.
3. Now move to column D and cell D3. Here comes the match function. The formula you fill
in here is as follows:
"=MATCH(E3,$C$3:$C$32,0)"
Here's what that formula actually means (always useful to understand formulas, makes it
easier to spot mistakes later). You are asking it to match the number found in cell E3
("1" in this case) with the rank number found in column C within the range specified. The
0 at the end means you want an exact match figure. Once again because of locking the
cells you can just copy this down the line and it will work it out.
4. Now to get the names to match. In column F you need to index the names to match the
numbers. So the formula in cell F3 is:
"=INDEX($A$3:$A$32,D3)"
This useful little tool tells it that you want to index the figures in those cells based
on the numbers found in the match column. And bingo your names are there.
5. Now to get the points figures (and any other data you want thats in another sheet or
whatever linked to that person's name). In column G, cell G3, you put the following
formula:
"=VLOOKUP(F3,$A$3:$B$32,2,FALSE)"
What this tells it (and this may be a very useful formula to know with what you're doing)
is to look at the value in F3, find the same value in the range A3 to B32, then find show
the data in the 2nd column in that range, in this case the points. The False name means
that it must get only that value matching that name. Don't ask me why but you get some
funny results if you don't put that one in sometimes. Note I used Vlookup, which looks in
a vertical direction down columns and returns a result in that corresponding row. The
command Hlookup looks across rows and find the result in the corresponding columns.
And that's it!
NB I opened my file in my 3.0 version of OpenOffice and everything worked perfectly. The
only thing I noticed was everywhere I used a "," in the formula it used a ";"
Hope that helps.
Edited 1 time(s). Last edit at 04/09/2013 08:01AM by Bruce D.