League Spreadsheet

Hi,

there are a lot of general questions in your post. It's hard to anwer these without knowing the structure of your workbook. Maybe you could upload a copy of your workbook with just two or three teams and a few week's worth of data.

Then get specific and explain what data gets put in and what you would like to see as a result of that.

You know how the archery league works, but we don't, so your questions should rather be along the lines of "I want cell X1 to display <fill in the blank> because cell A4 shows "abc" and cell C35 has "xyz".

You can upload a file by clicking "Go Advanced" below and then the paper clip icon.

cheers

Hi,

just to let you know, I'm working on a solution, but it'll take me a 'lil while. Please hang in there, I'll be back as soon as I have something halfway presentable. This being the weekend, and pre Xmas things to do with the kids and stuff... But I find the challenge intriguing and promise to come up with something you will be able to use.

cheers

Hi Jeremiahm,

see what you can do with the attached.

The principle is to separate data entry and analysis.

Scores sheet
Used for data entry only: Week, team, player, score etc.

Schedule sheet
Set up who plays who in which week

preview sheet
Look at the preview for any week. Enter the week number in B1. All else will calculate. Copy the pairing blocks to add more teams

results sheet
reports on results entered in scores sheet. Enter week number to view in B1. Copy the paring blocks to add more teams.

Teams sheet
Set up teams and other parameters for the league. This uses a technique called dynamic data validation. For each team name ad a block with team name on top and team members below, then assign the three players the range name = team name. Read up on dynamic data validation at http://www.contextures.com/xlDataVal02.html

Overview sheet
Pivot table of the scores sheet to summarise by team and week

This is just a starting point. I don't understand the handicaps and spot stuff, so I may not have applied it correctly.

If you need more help with this, yell away.

cheers

Hi,

forget the first attachment. With the rules you posted, I'm working on a different approach.

stand by ...

I'll be hopefully posting a suggestion by the end of today.

2003 it will be then.

One question: how many teams in the league? Ballpark ....

what happens when the adjusted score is a tie and the X's, too?

Jim 298/45
Bob 298/45

Don't know if that's possible at all, coz I don't know what X's are .... But if it can happen, how is it handled?

one point each
half a point each
no points either
?

new workbook uploaded in post on next page ....

Hi,

see what you can do with the attached.

The idea is that all scores and points are entered on the Scores sheet and all the other Sheets source info from there.

Preview and Results sheets are dynamic and will change depending on which week number is selected in B1. Thus there is no need to create individual sheets for each week. Just enter the desired week number and the data will be pulled from the Scores sheet.

Overview of teams per week on Teams sheet shows how the pecking order within the team can change from week to week. This information is used to figure out the shooter pairings.

I've put plenty of notes into the file, but don't hesitate to ask. Also, we need to nail the tie question.

hth

As I read your rules, you did not have handicap/spot in place last year, so change the value in the setup sheet from 80% to 0% when you play.

I've not hardcoded the 80% into the formulas, but left it configurable. I knew there would be a use for this

Not exactly. Since everyone knew how to play the game last year, I didn't need to type out the entire rulebook. So, the rules I sent you was from the year before. I used an 80% handicap (I used a 100% handicap the season before) last year when the I discovered the governing body, the National Field Archery Association, adopted an 80% handicap in indoor leagues.

I'm playing with it right now, but I'm having trouble getting through it quickly. It's pretty exciting though, because what I have got through, it appears to work flawlessly.

I'm going to put real team names and and real scores in it from last year tomorrow to really put it to the test and see how it does.

Thanks a bunch.

Oh, and if scores tie, and x's also tie, both shooters get the 1 point.

Oh, and one other thing that I haven't passed along. And, it caused us some trouble last year. Let me give you some background first. As you can tell, the spreads are calculated on revolving averages. If you just based your handicap on the first two weeks, which some leagues do, shooters have a tendency to shoot worse than they should those two weeks to reap the rewards of an artifically high handicap (sandbagging). We keep from this by have the averages change every week. The shortfall of this is when a shooter fails to shoot a score one week. So then they have a zero in their scores, which really effects their overall and really lowers it to a point that makes their spots huge, and unsurmountable. So, long story short, the average has to only look at cells with scores in them above zero.

I'll check it out

OK, seems that the configurable handicap % value comes in handy, in case next year you want to do 75%

I've implemented

Oh, and if scores tie, and x's also tie, both shooters get the 1 point.

and

the average has to only look at cells with scores in them above zero

the latter for both average scores and average Xs in all sheets that are affected, i.e. Preview, Results and Teams, so you may want to d/l the latest version, attached.

cheers

Jeremiah,
in your first post you say

You get 2 points for winning your head to head match. You get 1 point for tieing. Zero points for losing.

I just realised that I did not implement this, so Please Be Aware. It can be changed, though!! Although, at the moment I'm a bit confused as to the points, so you'd better spell it out for me again. Type slowly, January is still a while away ...

OK, I can go to bed then. Sleep will finally come. LOL

read you in the morning....

Hi,

you're doing it right, but it's a little work. For each team name, you have to define a range name after the team name has been entered in the list. The easiest way to do that is

on the Team page
select the team name and the three names below
click Insert - Names - Create and make sure that "top row" is checked.
Then go to the Scores sheet and use the drop down boxes for team and name to see the names appear
As soon as you've entered some scores for a player, their #DIV! errors on the team page will go away

try it out for one team and see the difference.

By the way, only the team name has the "no spaces" restriction. Players can have spaces, so you can use their real names ...

But another restriction for the team name is: no apostrophies or quotes. Because the team names end up as range names, you must follow the Excel rules for range names, i.e.

- no spaces or other funny characters other than underscore
- must start with a letter, not a number
- may not look like a cell address

so, Archer's_dream is not acceptable but Archer_s_dream is (yeah I know, it looks cr*p)
5Arrows is not allowed, but The_5Arrows is fine.

hth

Hmmm, Bob and Oskar are tied with the pecking order after week 1. See G33 on the Team sheet. Therefore, the Preview and Results sheet can't find a person with rank 3 and the formula bombs.

What should the pecking order be if two players have the same average?

After you tell me I can work on a solution....

Oh, and looking at the Schedule sheet: with 10 teams, you only set up 5 rows per week, not 10. Since each row lists two teams, you arrive back at your total number of teams.

Also, you need to set up the schedule for week 3, so you can preview week 3 ! The schedule is best set up at the beginning, for the whole season

cheers

Let me have a think about it. If it comes down to being a complicated thing, where I spend 80% of the time on 20% of the functionality, I'd prefer your approach to manually adjust the x's to iron out the tie.

OK,

I've implemented a tie breaker formula

=IF(COUNTIF(A2:A4,A2)>1,RANK(B2,B2:B4)+COUNTIF(B2:B2,B2)-1,RANK(A2,A2:A4))

If the score is a tie, we rank by Xs.

If Xs are a tie, the order in the list gets priority. Whichever player is higher in the list, will get the better rank

You can then fine tune this by ordering players either
- alphabetically
- tenure
- age

whatever, so that you find a fair and common ground how to break ties.

Attached version has the new formula in the Team sheet. To copy to your own file, copy D6-D8 in one block and paste onto your Team sheet over the same cells. D6, D7, and D8 have slight differences with cell references, so you need to copy them en bloc, and not just D6 to copy down!!!!

Then, paste that same block into row 6-8 in the other rank columns.
Finally, copy D6 to AE8 as one block and paste into D11, D16, D21 etc.

hth

Well, then ... one less thing to worry about during the xmas break. Happy holiday season!

Hi Jeremiah

I'm attaching the file again. I made these changes:

- created Range Names for the teams on the Teams sheet. It is explained below the table in row 65 ff.
By the way, the people names can have spaces! So I took the _ out in the names.

On the Scores tab you will now see the proper team names and people names in the drop down lists.

On the Schedule tab, you need to enter the week numbers for the pairings yourself. So if there are not enough pairings for week 1, just enter 1 in A2 and that row will then be the sixth pairing of week one. If you end up with more rows than get calculated in the purple columns, copy the formula down. I did not make any changes on that sheet, since I did not want to muck up your system. Just remember you need six rows for each week.

I've added a pairing block on each of the Preview and the Results sheets, so you now see 6 pairings. If your league grows: just copy rows 8-12 and paste it at the bottom. Then just enter the pairing number in the yellow cell.

I've deleted all the color range names and pasted a list of current range names on the documentation sheet.

Hope that helps.

cheers

edit: having trouble uploading the file. I'll need to try again later or PM me your email addy

edit #2: zipped file uploaded OK

Jeremiah,

I'v taken care of the #DIV/0 and the people not shooting in the first week. This now no longer triggers an error message, but there's a teeny catch: You have to have at least one player of a team with a score for the first week.

Example, the Rut Necks did not show up at all, therfore nobody scored anything, therefore the hacking order of the team could not be established, therefore the Preview and Results sheets would bomb.

I've assigned one point to a single Rut Necks member, but of course that 1 point will now be calculated into the average and bring the average down and the spot way up for the next round. Also, players who have not shot in week 1 and now have a score of 0 will have a really high spot. Maybe you can temporarily enter the player's previous year average in the week1 scores. Later, once you're well into week three and on, you can set all the week 1 scores back to zero for the players, and it will not affect any later scoring, averaging or ranking.

I've completely reworked the team pecking order ranking formula, because it produced wrong results. Now there is an additional column in the Teams sheet, where a combined score is calculated from Score and Xs and the ranks are now all good.

As for printing a weekly overview of scores per player: on the Scores sheet, click the drop-down arrow in A1 and filter by week. If you tick a number x, the list will be filterd to show week x only and you can print that on a single sheet. Would that help? It is of course possible to construct more elaborate and nicely formatted reports off the data on that sheet, and I'm happy to give you a few pointers if you let me know what you'd like to see.

J, I don't have access to Excel this weekend, but I'll look into it as soon as I do.

Jeremiah, I've come to a bit of a dead end. I've applied the tie breaker ranking formula as I found it on Chip Pearson's site, but I found that with three people having the same score and differenct X's, the formula does not produce the correct results. I've alerted Chip to the fact that the formula does not seem to be working when there's a tie between more than two values and I hope to get some feedback about a solution soon. Knowing Chip, I'd expect him to react fairly quickly and I will apply the fix to your spreadsheet ASAP.

I'm sure we'll get to the bottom of it and hopefully, as your season progresses, there won't be as many ties as in the first few runs....

OK, Chip Pearson has replied and has offered a different formula. Unfortunately it did not work either. Or, not always.

You got yourself a nicely complicated setup there, J!!

Anyway, I played around with it a bit more and I believe I have found a solution that works. Let that melt on your tongue: I fixed a bug in a Chip Pearson formula?! ... what the ...

Jeremiah, please check the attached, heart, lungs, CT scan, MRI, and get back to me.

cheers

PS. for the record: Chip's reply to my suggestion that the formula in the ranking spreadsheet was not reliable:

You are correct that there was a problem with the composite ranking when
using a secondary score for tie breakers. The problem lay in the calculation
of the composite rank, specifically where the formula uses
10^(MAX(LEN(C$6:C$15)+1)) to calculate a scaled decimal fraction to make the
composite ranks unique. The correct calculation is
10^(MAX(LEN(C$6:C$15),LEN(F$6:F$15)+1)). In other words, we need to
calculate 10 raised to the power of the maximum length of BOTH the primary
score (C6:C15) AND the secondary score F6:F15. Otherwise, the decimal
wasn't properly scaled. This problem manifests itself only under certain
circumstances, where the scaled decimal fraction overwrites itself with the
wrong value, dependent on the text lengths of the various scores. The
problem could have been circumvented with an intermediate calculation that
normalized all the scores to a value between 0 and 1.

and my amendment of the formula ...
=(C6/MAX(ABS($C$6:$C$15)))+(F6/(10^(MAX(LEN(C$6:C$15)+1))))+((ROW()/(10^MAX(LEN($C$6:$C$15)+1,LEN($F$6:F$15)))+1))

thanks for reading.

OK, re-worked and re-attached.

Both D Broxterman and B Haas were ranked 1, so there was nobody on rank 2, so it croaked. I adjusteded the ranking formula (yet again) and applied it to all places where it belongs.

Do you see how McNelly's Rangers have two members with the same score and the same x's and still have nicely differrent ranks? I think this is finally working.

hoping for the best ....

Relief! When I saw the thread pop up again in my UserCP I thought "Uh-ooh, what now?"

Guess you can concentrate on shooting straight, then, instead of worrying about a silly spreadsheet.

Thanks for checking in and reporting back. Glad it's working for you. How are the scores?

You can do excactly what I would to to create a blank copy: download the latest version and delete all the data.

hoytshooter5
welcome to the forum but Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.