Forum Discussion
EXCEL FORMULA
Also will the cell address where I enter the score need to entered in this formula.
Example
The score goes in c5
I want the points to wind up in c26
CURTIS775 and where is the Par value. It would be best to include a sample sheet because Par would be listed 1x per hole but there will be multiple players so the references will have to be a mix of relative and absolute references.
I have the pars running across the top of the page and will enter the scores just below.
I want the resulting points display in the bottom section. Is this possible?
CURTIS775 see attached
In sheet 4 I used the "old" style of formulas were you create a formula and copy or drag down (and/or across) to fill in the rest of the formulas. In sheet 3 I inserted some array formulas where you only enter the array in 1 cell and the rest of the array 'spills' over to the other corresponding cells.
That all said, I also moved the players in the upper score table be in the same order as the player list in the lower table to avoid needing a lookup equation. Also, I notice a couple of "error" in the scoring example and a couple cases of that 2 under par (eagle) that was scored as a '5' but the equation I proposed scores it as a '7' (see orange cells on sheet 'event 2'). If you prefer it max out at 5 points then the equation could be tweaked to be:
=MIN(MAX(3 - 2 * (C2 - C$1), 0),5)but my clever 2-d array equation on sheet 3 would be a bit more difficult and would be easier to just use an array equation for each hole.
I also updated the equation for YTD Total to use "3-d array" to make it simpler:
=SUM('event 4:event 1'!P17)You can get fancy and make that a general equation to automatically update as you add additional sheets but I won't go there.
Hope this helps.