Change to Title, to better reflect my request
Change to Title, to better reflect my request
Last edited by flyerpicker; 04-22-2017 at 10:13 PM.
B C D E F G I J K L M N O P Q R S T U V 2 Name Today Var Quota 10th 13 Apr 6 Apr 30 Mar 23 Mar 16 Mar 9 Mar 2 Mar 23 Feb 16 Feb 9 Feb 2 Feb 26 Jan 19 Jan 12 Jan 3Alan 9 (22) 31 14 32 32 33 31 29 30 29 4Barb 24 15 9 10 11 6 8 4 5 9 7 7 10 10 6 5Cain 25 15 10 11 9 11 8 11 8 7 10 11 11 8 6 9 7 6Dana 26 12 14 14 16 12 15 15 14 7Eric 7 (6) 13 11 11 11 12 12 12 13 16 11 12 13 16 13 16 8Fran 25 0 25 14 24 26 24 27 23 21 24 9Gary 17 12 5 14 3 4 5 6 4 0 2 7 10Hana 26 2 24 14 24 23 23 24 23 24 22 24 26 11Ivan 18 8 10 12 9 7 8 10 8 6 12 8 7 15 12Jane 9 (7) 16 10 15 16 14 14 18 16 15 18 15 14 17 17 14 13Kent 17 6 11 10 11 8 9 11 9 8 9 12 11 10 7 9 14Leah 27 8 19 13 19 19 18 18 15 15 15 19 19 20 13 15Mark 10 (7) 17 12 17 15 18 16 18 15 13 16 18 13 16Nina 29 2 27 13 23 28 23 27 24 26 25 22 27 27 24 17Otto 6 (16) 22 13 20 21 22 22 23 20 20 23 23 19 21 18Peri 14 (10) 24 14 22 22 24 24 22 20 20 24 22 26 19Quin 5 (10) 15 14 15 12 16 15 15 13 15 14 11 16 20Rene 21 (11) 32 14 31 33 32 31 31 29 33 28 21Seth 13 6 7 14 6 6 6 5 5 5 5 8 9
See attached.
Entia non sunt multiplicanda sine necessitate
Dear Shg,
your reply looks absolutely awesome. I do have a few questions to help me better understand how this works. If you have time, I really would like to talk with you about your sheet. If you prefer to not call, I can put them into a typed reply. Certainly thanks for your efforts thus far. Jim 502-593-0395. If I dont answer, leave a message, letting me know when to call you back. :-))
That, please.... or I can put them into a typed reply.
Yours is a much newer version than my 2003. I tried a few things like inserting a new row (as in a new member, or just typing a name) and mine crashed. I would upgrade my excel if necessary to make this work. I have a 4 year old MacBook Pro.
I guess I just need some basic instruction on how to enter data, and then how to work with the VAR column to see who won. If I clear the contents of the TODAY column, then the VAR column populated with the player’s quota expressed as a negative number.
But anyway…
When I sit down at the clubhouse and enter the scores:
-which column do I enter scores in? I am guessing I should enter in the TODAY column, then have that data migrate to the column right of column G.
- is it possible for the VAR column start out blank as I begin to enter scores? Otherwise, I will need to “overlook” non-players data that is visible.
Speaking of sorting, when I perform a descending sort on the values in the VAR column, they result does not appear to be in any particular order.
What is the “10th” column number? Will I need to see it for any reason?
Again, thanks very much…. Again, feel free to call me.
Jim
Here's a version for Excel 2003.
Yes, enter today's scores in the Today column. When you're ready to move on, copy the Today column, insert it to the left of the leftmost dated column, add the date as the header (or not, they are not used), and clear the scored in the Today column.
Done.is it possible for the VAR column start out blank as I begin to enter scores?
You have to sort the whole table. I deleted the blank column to make it more obvious.Speaking of sorting, when I perform a descending sort on the values in the VAR column, they result does not appear to be in any particular order.
It needs to be there, but you can hide it.What is the “10th” column number? Will I need to see it for any reason?
Loving your work!
Questions:
* a player should have a quota before the round is played and then his quota should be updated after his score is posted. When does that 'update' occur?
* I understand I can hide the '10th' column, but I am still curious what the number means, or should i just not worry about it?
* when a new player joins the league, since he has no history, we were thinking we would just assign him a quota and go forward. Is the proper way to do this to enter 5 rounds with that pre-determined score?
Last edited by flyerpicker; 04-23-2017 at 08:34 PM.
When you copy the Today column and insert it among the other scores.
It locates the column of the 10th oldest score.
Sounds right to me.when a new player joins the league, since he has no history, we were thinking we would just assign him a quota and go forward. Is the proper way to do this to enter 5 rounds with that pre-determined score?
I think this is a better layout:
B C D E F G H I J K L M N O P Q R S T U 2 Name 10th Quota VarHideMe 20 Apr 13 Apr 6 Apr 30 Mar 23 Mar 16 Mar 9 Mar 2 Mar 23 Feb 16 Feb 9 Feb 2 Feb 26 Jan 19 Jan 12 Jan 3Gary 14 27 (5) 22 25 24 25 28 25 27 29 27 4Otto 14 11 (3) 8 10 10 11 12 12 10 5Kent 14 15 (3) 12 13 13 16 15 14 15 13 12 15 6Fran 12 30 (2) 28 29 27 30 28 31 25 28 32 24 27 7Hana 13 17 (2) 15 18 16 18 14 14 15 20 14 11 9 14 8Leah 12 19 (1) 18 18 20 17 17 19 15 17 18 19 22 22 18 9Jane 13 22 (1) 21 20 19 24 24 20 19 21 22 22 18 24 10Dana 14 14 (1) 13 15 17 12 16 12 14 11 12 11Cain 14 21 (1) 20 22 20 20 21 21 19 19 21 24 20 12Peri 14 22 (1) 21 24 23 21 22 19 19 23 21 13Rene 14 7 0 7 7 7 8 11 5 6 14Eric 13 10 0 10 12 9 11 10 10 10 11 9 10 10 15Nina 14 9 1 10 5 7 6 6 11 6 8 10 10 10 16Mark 14 29 25 27 30 28 27 28 32 27 17Quin 13 20 17 20 20 22 20 19 17 22 16 19 17 18Ivan 14 25 24 27 24 26 25 25 23 19Alan 14 10 9 7 12 9 10 12 9 6 11 20Barb 14 12 10 11 10 11 7 13 13 12 13 10 21Seth 14 13 11 10 10 12 9 10 14 7 13 16
To add a new date,
o Insert a column to the right of the HideMe column. Quotas will recalculate and variances will be blank.
o Add today's scores in the inserted column. Variances will calculate as you enter them.
o Sort the whole table ascending by the Variance column.
Get a newer version of Excel and try that.
Evidently my problem is my mac 2011 version. i can insert rows, but when i enter a name in the first column, I get a crash.
Can you tell me what version you created this workbook in? I sort of hate to spend the money to upgrade for this one golf workbook only, but will if i have to. Unless you have some way to write your magic in an 2003 version.
Last edited by flyerpicker; 04-24-2017 at 09:50 PM.
Dear Shg, everything works as advertised, but still the same issue: I am able to insert rows (as in adding new players) but when I enter a name, excel crashes and generates a crash report. Any ideas? I have to have the ability to add more rows, with names. Jim
I don't have any problem with either the xls version (opened in Excel 2003) or the xlsx version (opened in Excel 2010).
Maybe someone else will wander along and try.
Minor updates attached.
Hi,
Both versions also crash for me using Excel 2011.
Don
Please remember to mark your thread 'Solved' when appropriate.
Isn't that curious, thank you. Anything to indicate why?
Can someone try in a Windows version?
I'm using windows and both open for me. Excel 2016 and 2003
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Seems to be a Mac thing, flyerpicker, and I've never touched one, so have no suggestion.
I only had time to test briefly last night. I will endeavour to determine the issue later when I have access to my Mac again.
A double named range 'scores' in which one the formula contains only #ref 's doesn't help I assume.
Nor does it hurt. It's an orphaned name from a deleted sheet.
Did you ask the OP to test a file in which no dubious named ranges occur ?
It doesn't make a difference- the crash still occurs.
If you turn off the 'Extend data range formats and formulas' in Excel Preferences, the crash doesn't occur. Alternatively, if you fill the formulas down from the row above before entering a name in column B, it also seems to be stable.
Last edited by xlnitwit; 04-27-2017 at 06:27 AM.
Hello Shg and others…
Took a short vacation, sorry if I held you guys up. It appears the problem of inserting a row and/or inserting a name causing a crash has been fixed by the aforementioned change to Excel Prefs. Thank you.
So where are we now? I attached SHG’s sheet, newly formatted and using our live names. All the math seems to work. Notice that I added a clip at the bottom to compute payouts. All that remains is for me to enter historical data for all the players.
SO, just a small last issue: I eliminated the actual date row—for simplicity. However, when I take out the very last date, the math creating the quota in column C goes ###. So I left the date in cell R1. Can we eliminate that date and still have formulas working?
You can use numbers instead of dates as the row headers, just don't format them as text. Select F1:R1 and format as General, then enter numbers.
Hi SHG…
Not sure if I did anything wrong, but here another challenge:
All works fine as I insert the new column and enter that day’s scores in the new column. After all entered, sorted, and payouts made, it comes time to “slide all the scores to the right” and prepare for the next week’s entries, which should include updated quotas.
I can’t see where that is happening. The quota is not updating like it did on the samples you last posted.
Anyway, hope you can help. I am LOST! I have attached the current live sheet, and I have it backed up
Jim
Hello Shg,
Need your help.. see post # 26. Jim
It works fine for me. Can you give a specific example of a row where the quota should change but doesn't?
well, it appears i had a brain fart. Yes, the quota does update the moment I insert a new column.
So, as i insert a new column each week (for up to 30 weeks) how should I manage the size of the sheet? Could i hide columns that march way over to the right? if you were me, what--if anything--would you do to improve this sheet? We like it really well, and it goes to work this Thursday.
thanks for everything...
Jim
Glad it works for you, you're welcome.
Hello Again My Friend!
First , all your work has seen good times. My group of 50+ golfers are amazed at this spreadsheet that you worked on so hard. Not only is it more accurate than doing napkin math, it’s also easier and faster.
Since you saw it last, it has grown in features, which I will explain below. But for the basic work you created that records the player’s scores, that portion is working just great.
As you can see by looking at the attachment, I have entered about 5 weeks worth of playing. But I do have 3 very minor issues that I have been working around:
1) I can’t sort the VAR (variance) column when populated. There must be something in that column that interferes with the sort function.
2) The character (a zero) currently in Z1 must be doing something, because when I try to clear or delete it, the rest of the sheet goes nuts. Can this character “go away”?
3) The players currently in rows 29 and 39 are ‘new’. I have been entering their scores and managing/entering their quota manually. Per our club rules, a player’s scores are a simple average (rounded down) until completing 5 rounds, at which time your formulas take over which is the best 5 of the last 10 rounds, rounded down. Is there any way to let the spreadsheet do this, which is to begin a player’s history with the raw average (to determine the quota) until after the 5th round and then switch to the existing formula?
The other thing I am prayerfully requesting of you is looking for a suggestion to improve the utility of the sheet: As you can see, I have built a “payout” section in the name column. It’s located there because as we go forward in time, the spreadsheet “grows” to the left and to the right of the name column. Adding columns to the left are to record the players earnings history and columns added to the right are the historical scores. So the payout section has to “live” in the name column, otherwise it gets moved around.
My problem is: when I complete a given week and “record” the scores by inserting a new column, I have to do lots of busy work, such as creating new headings, move cell shadings, re-sorting, etc.
I guess I want my cake and eat it too! I am asking your opinion about how to minimize the busy work after each round. Is there a better way to arrange all this data? Point being--each week I would really like to just enter the scores, the money payouts, and then do something that records everything (like inserting the new column) so that the sheet would be ready for the next round.
Am I asking for too much? I again thank you in advance for your assistance. Feel from to call me 502-593-0395.
Jim Allen
Hi Jim,
I too am an old golfer and have done many tournaments using Excel. I've accomplished some of what you need by keeping two different sheets in the same workbook. The first I call Scores for the current day's round of golf. A second, and the one I think you need, I call Rounds. When the day is done and all scores are in and money handed out, I simply copy my (current day scores) to the bottom of the Rounds sheet. This then allows me to accumulate many rounds into a single table.
I know how you are keeping past scores on a single row for each player, but this leads to much harder formulas and complications. I think you are expressing this by needing a 5 round rule to determine handicaps.
You asked for an easier way to arrange your data, and I'd say that is to enter your data as one row per round. You will need to add a column of "Date Played", "Player Name", "Handicap", "Gross Score", "Net Score". Yes, you will need to add a date for each row and use Validation for Player Name, BUT this will then allow Excel to do much easier formulas. You will also then be able to add a single round to a sheet that collects all rounds.
If you Private Message me with your email, I can send you a few examples of ones I current use, so you can see what I'm working with.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Jim,
You asked for a better way to collect your weekly data and attached is how I'd start doing it.
You might need a cute formula like shg supplied but it would go on the Rounds sheet to be copied onto the Scores sheet.
Start with this kind of data layout to make the whole process easier.
Golf Buddies June Better Way.xls
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks