Excel Junkies

[Jon Brakel]

This kind of goes with TD corner but I wanted it to have its own thread. One of the tedious things I have to do as a TD is put the place of finish in the TD report. They really need to program this column but they haven't yet. It would be easy except for all the ties. Ties go in like so: 1,2,3,3,3,6,7,8,9,9,9,9,9,9,15,16, etc. I could use the rank tool in Excel and program it myself but I can't figure out how to replicate the function so that it continues to use the same array of numbers. For example I use =RANK(E1,E1:E7,1) for the first number where E1 is the number to rank (score) and E1:E7 is the array of scores in that division and 1 is the ranking method (it will give me 1st on down). When I go to replicate that function it changes the array and obviously I want the array to stay the same within the division. Is there a way to replicate without changing the array? I have included a blank TD report (go to the Scores tab) in case you want to play around with the page that I'm talking about.

[tacimala]

I haven't looked quite yet but could it have something to do with the $ operator in Excel where you specify that it can't go outside of that area? That's the first thing that comes to mind without looking at it further. Let me know if I need to expand further.

[stpitner]

yeah, it's the $ that you need.

=RANK(L24,L$24:L$524,1)

The dollar sign before a row or column will tell the formula that it is fixed.  So if you did =$L$24 and you copied that value into another row and column, it will still be =$L$24 instead of adjusting for the move.

My formula up above will automatically update itself for the new row but continue to use the rank of the entire selection of data.  Of course you would want to change your array of data to be just the one division that you want.

-Scott

[Jon Brakel]

Sweet! Thanks for the help guys!

[Tom McManus]

What does the column width need to be to make it a 1 to 1 scale with the standard row height?  Thanks.

[stpitner]

12.75 (17 pixels)

[Tom McManus]

12.75 (17 pixels)

Is that 1.275?

[stpitner]

1.71, sorry, it goes by a different scale.  The alternative route would be to select the column (or columns) that you want to resize, and click and drag at the line that separates the columns where they are labeled and resize it to 17 pixels.

[Jon Brakel]

So, would one of you be willing to program a permanent solution? Do you have those kind of skills? I could check with David Gentry if they would accept a solution for automatically ranking people.

You'd have to write code that would key on the division code, say MA3 and then use the array of numbers for everyone in MA3, then rank accordingly. Since I've seen the TD report evolve from a spread sheet that had almost no calculations to what it is now, I'm sure such coding is possible. The TD report is in the OP of this thread if you want to look at it.

[stpitner]

hrmm... if they had it separated by division then it would be pretty easy, but when you can arbitrarily enter any players name for any division it makes creating the array for the ranking to reference to a bit more difficult.

Maybe a conditional can be used with some sort of lookup function?  Anyone?  I know how to do this with macro's, but the PDGA is *not* going to want to start incorporating macros into their spreadsheets.

[Jon Brakel]

hrmm... if they had it separated by division then it would be pretty easy, but when you can arbitrarily enter any players name for any division it makes creating the array for the ranking to reference to a bit more difficult.

Maybe a conditional can be used with some sort of lookup function?  Anyone?  I know how to do this with macro's, but the PDGA is *not* going to want to start incorporating macros into their spreadsheets.

One of the columns is for division. I guess I don't understand what you mean.

[stpitner]

Right.  Let's say the TD enters it as following (Division, -Rank-, Name, total score):

MA1 (Rank) Joe Schmoe 124
MA3 (Rank) David Barish 138
MA1 (Rank) John Doe 122
MA1 (Rank) Jane Doe 130
MA2 (Rank) Steve Someobyd 132
MA3 (Rank) Scott Pitner 136

and so on.  They just entered it as the pre-registrations came in or something.

When you go to enter the ranking code, it needs to know the range of values to compare to for the ranking.  Since you only want MA1 stuff to rank with other MA1 scores, you'd have a problem compiling the array of scores because they are intermittent.  There needs to be something where it is =RANK(myScore, RANGE(x1:x500) includes only if the specific row division = myScore's row division (MA1).

If you as a TD enter all your stuff and know how to use the sort command, then you can use the rank example formula I gave above and just change the array to fit to only the players of a certain field.  I don't know if there's a generic formula that can handle unsorted data or even an undetermined field size unless there were separate areas (not just codes) for each division.

it's hard to explain, but I gave it my best.

[pickax]

I wasted way too much time on this today...

In C24:
=IF(G24="","",(SUM(IF(($A$24:$A$524=A24)*($L$24:$L$524<L24),1,0)))+1)
When you enter this formula, you need to hit Ctrl-Shift-Enter rather than just Enter.
You can then proceed to copy this formula down the column

This will handle all of the data intermixed, etc.

[Jon Brakel]

I wasted way too much time on this today...

In C24:
=IF(G24="","",(SUM(IF(($A$24:$A$524=A24)*($L$24:$L$524<L24),1,0)))+1)
When you enter this formula, you need to hit Ctrl-Shift-Enter rather than just Enter.
You can then proceed to copy this formula down the column

This will handle all of the data intermixed, etc.

I don't swear very often but that is F-ing excellent! Way to answer the call and spend too much time on it. I have submitted the idea to the PDGA to add to the TD report and let them know that you wrote the code, Mike. If they put this in the official TD report, TDs across the world will owe you for saving them from many tedious entries!

[stpitner]

hmm, ctrl-shift-enter is something that I haven't used before.  I know that it added curly braces to the cell contents, but what exactly does it do?  This feature might be something that I could use at work.

[pickax]

Normally you put the formulas in and lock them. The only small problem I see with this solution is that it won't handle final-9 type arrangements where the top n players play additional holes for final placing. In that case, the formula needs to be overwritten (which means the formula probably needs to be unlocked)

Scott- ctrl-shift-enter is for doing array formulas. Think vector operations and you should be ok. The biggest problem with them
is that any function that takes an array will end up collapsing the vector rather than returning the vector. e.g.
sum(if(and(vector1=value,vector2=value),1,0))
My preference would be that only the final sum() collapses the vector, but and() also collapses it to one value resulting in a final value of only 0..1 and not 0..len(vector)
The other gotcha with array formulas, is that you always have to use ctrl-shift-enter or otherwise it reverts to a normal formula.

[Jon Brakel]

I was going to suggest that they leave the formula unlocked because of ties broken on playoffs also.

[pickax]

The best way to use it would be to enter all of the regular rounds, and then lock it down by converting the column to values. (Copy data and then paste special as values). Then enter any final 9 stuff and/or take care of ties.

[Jon Brakel]

The best way to use it would be to enter all of the regular rounds, and then lock it down by converting the column to values. (Copy data and then paste special as values). Then enter any final 9 stuff and/or take care of ties.

OK, I can do that. Not sure how they'd communicate that to the other TDs. Most TDs are also disc golfers!!!

[SERG]

I also need assistance in Excel. In our leagues at Parkside we use the 10 best scores out of 12 weeks. Does someone know how I can do this through a formula or would I have to do it manually by counting each week?

I've attached a copy of the score file so you can see what I mean.

Thanks.