Help with functions in a Bowl Pick Ems

**pgreenway** · 12-22-2010, 02:24 PM

I am doing a Bowl Pick Ems Sheet for me and my friends. I have everything set up except on the updated standings tab. On the left side of the updated standings sheet it looks like this:

A B C
Points Player Current Order
4 Paul 1
2 Tony 8
3 Chase 3
1 Chris 11
4 Austin 1

which sums up the total points in column A, the name in column B and using the rank function places them in a current order in column C.

Now, on the right side:

K L S
Rank Updated Standings Total Points
1 Paul 4
2 Chase 3
3 Tony 2
4 Chris 1
5 #NUM #NUM
6 Paul 4

and then repeats 1-4 as it goes down to the 14 people. Now, on the left side with the total points, player and current order everything works great.

My questions are this:

How do I keep column K (Rank) up to date with the current order in column C?
How do I keep the Total Points up to date to match up with column A in descending order including potential ties?
After that, can't I just use VLOOKUP to match the column L (name) with the (total points) in column S as I am currently doing?

I would really appreciate any help you guys could give, thanks in advance!!!

**ChemistB** · 12-22-2010, 02:56 PM

Here's what I would do.
Insert a Column G (you can hide this later) and in G3 dragged down

=RANK(A3,$A$3:$A$16,0)+COUNTIF($A$3:A3,A3)-1

This ranks them but gives each participant a unique number, so if 4 people are tied for first, they are ranked 1,2,3,4. Again this column can be hidden.

Next, In K3 down,

=INDEX($E$3:$E$16,MATCH(ROWS($A$1:A1),$G$3:$G$16,0))

In L3 down,

=INDEX($B$3:$B$16,MATCH(ROWS($A$1:A1),$G$3:$G$16,0))

In S3 down,

=INDEX($A$3:$A$16,MATCH(ROWS($A$1:A1),$G$3:$G$16,0))

Does that work for you?

**pgreenway** · 12-22-2010, 03:58 PM

ChemistB,

You are amazing, that is exactly what I was needing. Thank you so much. I completely understand the functions you used too. I couldn't wrap my brain around it but adding a new column makes sense. Thanks so much!