Calculate points awarded based on several conditions

[gingrbredman]

My team at work created this really fun golf league with all kinds of crazy rules and I developed a spreadsheet to calculate a lot of the scores. In the attachment, down to about row 25 is a lot I learned from this site and the awesome people that contribute to it. I hope one day I will be able to help out as much.

I have calculated the net scores, strokes given based on hole handicap, but where I hit a roadblock is to automatically calculate the points awarded per hole. We assign points per hole to a team, net of handicap, based on certain criteria. I included that in the worksheet. It seems complicated but really is as easy as...

Teams who TIE a hole get ½ point each as long as the scores are par or lower​

Teams that win the hole with a par get one point​

Teams that win the hole with a birdie (1 Under Par) get two points​

Teams that win the hole with an eagle (2 under par) get three points​

Teams that win the hole with a bogey (1 over par) get NO points​

Teams that split a hole, with anything above a par get NO points​

I thought I would use and IF/THEN, but not sure how to stack them all so each rule is met. I tried to create a separate table to identify scoring, but that didn't work either. Any help starting in row 28 would be appreciated. I did put the "correct answer" for POINTS starting on row 42 to check and see if my formulas were working, to no avail.

Second point. We assign 1 extra point to the team that won the most holes in the front 9, the back 9, and total for the day. For the hole by hole formula, I put in an IF statement for each team, and this shows me who won the most holes in the front, back, and total, but am struggling to calculate that means "1" point. The total for example is team #1 would have gotten 1 point for the front nine because they won 3 holes and the other team only won 2. Again, any help at all with this one is much appreciated!

The green and red conditional formatting just visually show better or worse than par. The yellow is where I'm struggling to calculate.

 

[gingrbredman]

Well, I at least answered part of my question. Regarding point 2, it was a lot simpler than I thought. Not sure why I was over thinking it. A simple IF statement to see which is higher or lower and add them all up.

=IF(N32>N33,1,0)+IF(X32>X33,1,0)+IF(Y32>Y33,1,0)
Front 9 Back 9 Final Score

I saved a new version of my sheet, still trying to figure out how I can calculate a score for each hole, based on the rules we have in place. I have been trying to nest all the rules in an IF/THEN formula but haven't gotten there yet. Also thinking maybe a table that gets created and then use that for the results, but still struggling getting there.

 

[bosco_yip]

=IF(N32>N33,1,0)+IF(X32>X33,1,0)+IF(Y32>Y33,1,0)
Front 9 Back 9 Final Score

1] In your worksheet F36, sum of Front 9, Back 9 & Final Score
=IF(N32>N33,1,0)+IF(X32>X33,1,0)+IF(Y32>Y33,1,0)

2] You can use this shorter and give the same result.

F36: =(N32>N33)+(X32>X33)+(Y32>Y33)

And

F37: =(N33>N32)+(X33>X32)+(Y33>Y32)

Regards

 

[gingrbredman]

1] In your worksheet F36, sum of Front 9, Back 9 & Final Score
=IF(N32>N33,1,0)+IF(X32>X33,1,0)+IF(Y32>Y33,1,0)

2] You can use this shorter and give the same result.

F36: =(N32>N33)+(X32>X33)+(Y32>Y33)

And

F37: =(N33>N32)+(X33>X32)+(Y33>Y32)

Regards

That works even better, thanks!