I'm still confused

[Guest]

I know some have helped on this before, but I cant seem to get it right.
Heres what I have

A Name Age Time Score
B Bob 8 12.2 56
C Sue 12 13 72
etc

NAME is in column C
AGE in column D There are only three ages 8, 10 and 12
TIME in column K
SCORE in column L
There is some unimportant info in the other columns

I would like to have it show me the highest four scores in each age group,
for a total of 12. I would like this to be a function, not resort the whole
list, so it is up-to-date all the time. In the event of a tie in an age
group, the lowest time ranks higher.

Any help?

 

[Pete_UK]

Did you check out the link I gave you the other day, i.e.:

http://www.cpearson.com/excel/rank.htm

I thought this explains things very well, though you might need to
tinker with the formulae as you have 3 age groups in the same data
set.

Hope this helps.

Pete

 

[Guest]

Yes, thank you. I went there, and I have the ranks. What I cant figure out
is 1. How to seperate them by age and 2. How to make use of the tie breaker
ONLY if needed.

 

[Pete_UK]

I would suggest that you need a few extra columns. One could turn the
age into a simple letter code A, B or C. Another column could subtract
the minimum time for each age group from the time for each person (to
get the correct ordering). A third could then join the age code with
the points scored (maybe with leading zeroes) and the time difference,
so you might end up with something like:

A056002.4 which represents Age group A, 56 points, 2.4 seconds over
minimum

Then a ranking of these will pull all the A's together and compare the
points first of all, and only if that part of this code is the same
for two or more people would the time difference be taken into
account.

As I said before, it is more complex because you have 3 age groups
mixed together, but it is achievable, though you will probably need
array formulae to effect the sort making use of the LARGE function.

Hope this helps.

Pete

 

[Pete_UK]

A couple of changes from last night's posting - don't use letters for
the age group, and for the time difference you need to subtract it
from a number larger than the timings in your data. I set up a test
sheet1 using the columns you specified earlier, and put this formula
in column P (so that it is out of the way from printing):

=((D2-6)/2&TEXT(L2,"000")&TEXT(999-K2,"0000.0"))*1

This gave me numbers like 10560986.8 and 30720986.0 for your two
examples, where the first number (1, 2 or 3) represents the age group,
the next three digits are the points, and the last five digits
represent the time difference (subtracted from 999 seconds).

Incidentally, if you put this formula in Q2 and copy down:

=LARGE(P$2$44,ROW(A1))

you will see all the numbers beginning with 3, followed by the 2's and
finally the 1's, effectively sorted. I set up a table with data in
rows 2 to 44, so adjust to suit your data.

In Sheet2 I assumed you would want some headings to separate each
block of top-4 age groups, so I put this array* formula in P5 (again
to keep it away from any printing):

=LARGE(IF(Sheet1!D$2$44=8,Sheet1!P$2$44),ROW(A1))

*As this is an array formula, then once you have typed it in (or
subsequently edit it) you need to use CTRL-SHIFT-ENTER to commit it
instead of the usual ENTER. If you do this correctly then Excel will
wrap the formula in curly braces { } when viewed in the formula bar -
you must not type these yourself.

This formula can then be copied down into P68, and it is finding the
top four in age group 8. I put this array* formula in P13 and copied
it into P1416:

=LARGE(IF(Sheet1!D$2$44=10,Sheet1!P$2$44),ROW(A1))

These will find the top 4 in age group 10. This array* formula went
into P21 and was copied into P2224:

=LARGE(IF(Sheet1!D$2$44=12,Sheet1!P$2$44),ROW(A1))

and this is looking at the 12 year olds.

I put this formula in Q5 of Sheet2 and copied it down into cells
adjacent to those where the array formulae were:

=MATCH(P5,Sheet1!P$2$44,0)

and then in A5 under a heading "Name" I used this formula:

=INDEX(Sheet1!C$2:C$44,$Q5)

This was copied into A4:A6, A13:A16 and A21:A24, to give the names of
the top-4 in each age group. If you also want to bring across the age,
points or time, then you can use these formula in row 5 and copy down
as appropriate:

=INDEX(Sheet1!D$2$44,$Q5) - age
=INDEX(Sheet1!L$2:L$44,$Q5) - points
=INDEX(Sheet1!K$2:K$44,$Q5) - time

If you use more than row 44 then you will have to adjust the ranges in
all these formulae to suit your data. I think this does all that you
wanted.

Hope this helps.

Pete