Golf Formulas Help Please

Our Tuesday golf group is trying to set up a spreadsheet to keep up with our match, calculate how many points someone has to pull the next week and who wins the money by way of percentages.

If Ray has to pull 28 points
Wayne has to pull 16
Edd has to pull 19
LYNN 28
MOE 22
CHRIS 22
JIM 28
HARRY 23
PETE 28
KYLE 9
STEVE 22

If anybody pulls 2 or 3 points over what they are required to pull they get one point added on the next week. If they pull 4,5,6,7 points over their required amount they have to pull 2 points more the next week and if they pull 8 points or more they would have to pull 3 points more the next week.

The same is on the negative side, if they pull 2 or 3 points less than they were required to pull, they would have to pull one less the next week etc.

Also, the percentages of winning that week that needs to be calculated would be
if we have 6 or less players we would divide the pot by these percentages for first second and third places. 50 30 20
7 or more players 40 30 20 10

I have no idea how to set this up, can anybody help me?

Hi, edorminey - a small sample workbook (NOT a picture) would help us to solve your problem quickly and accurately.
Remove any sensitive or extraneous info, just show us the data you're collecting and mock up some results to clarify what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!

ps. What does "pulling points" mean?

See post#5 for an updated version of this formula.
This ARRAY FORMULA* will calculate the winnings for each player. Paste it in C40 and copy down:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please Login or Register  to view this content.

Back in a few minutes with more...

OK, this regular formula determines Next Week's Pull. Paste it in C5 and copy across, then down:

Please Login or Register  to view this content.

...and here's a improved version of the ARRAY FORMULA* used to calculate Winnings. Paste in B40 and copy down:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please Login or Register  to view this content.

1) Click Freeze Panes on the View tab and select Freeze First Column to keep column A permanently visible as you scroll horizontally. Excel remembers worksheet scroll positions when you save, so you could just use the horizontal scrollbar to hide any "old" columns periodically.
Alternatively, if you want to mess with macros, I could write a VBA procedure to do it automatically when you open the workbook. A separate procedure could also automate the new player row thing at the click of a button.
(NOTE: If you want to go with macros, may I suggest you start appropriately-titled new threads in the VBA forum with workbook attached to gather fresh ideas. For example: "Need macro to scroll to most recent column.","Need macro to add new row to 3 identical lists.")

2) Double-click on any EDGE of the textbox to select it. Then press Delete.

3) Select any cell in the row BELOW your insertion point. Then in the Cells section of the Home tab, click the Insert drop-down arrow and select Insert Sheet Rows.

Sorry, column addresses had to be changed from absolute to relative in the Winnings formula. My mistake.

I've also switched to dynamic named ranges for the TARGETS and POINTS sections of each column. These should adjust automatically to include new rows, based on the number of names in the TARGET section of column A. You can view the definitions in the Name Manager on the Formula tab.
Here's the new Next Week's Pull formula (in C7, copied across and down):

Please Login or Register  to view this content.

...and the new Winnings Array Formula. Paste in B44, press CTRL+SHIFT+ENTER, then copy across and down:

Please Login or Register  to view this content.

Your requirements weren't very clear, but I guessed at your intent:
1) Row 1 is POT amount for EACH week.
2) Row 2 (formerly row 39) is # of PLAYERS for EACH week.
3) Row 3 is BUY-IN for EACH week.

This should work.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

May I suggest you google "excel lock cells".
If your original question is answered, please mark your thread as SOLVED (Thread Tools up top). Thanks -Lee

You're most welcome. Just for future reference, when using this forum:
1) Be specific with Thread titles. Example: "Need golf scoring and handicapping formulas"
2) ALWAYS provide a sample workbook. Include Data to test and Simulated Results to demonstrate what you want.
3) Mark your threads as SOLVED once you have an complete answer to the ORIGINAL question.
4) If you wish to expand on the problem, start a NEW thread (with a link to the first thread, if appropriate).
"Fresh" threads appear on the New Posts list and tend to get the most interest.
5) Clicking the Add Reputation star below helpful posts is a nice way to say thanks to those who contributed!