I'm not sure how to ask because I may be using the wrong formula entirely. I created a simple spreadsheet to track a modified golf match for my son's Jr. PGA league. I only care about his match which consist of him and a teammate against another 2 person team. They have 3 matches in a 9 hole event. Each match is 3 holes. Each hole is a "Win, Lose or Tie" and after the 3 holes are completed, that match is either a "Win, Lose or Tie", "1 point, 0 points or .5 points" respectively. However, the league wants to see the strokes per hole to confirm if each hole is W,L or tie. At first I had the points column add the 3 holes and if team 1 was less than team 2, they would get 1 point, if greater than, 0 points, and if equal then .5 points. However this doesn't work since we are tracking the actual strokes. In the example: if team one scores 4,6,6 on holes 1 thru 3 and team 2 scores 5,4,7, Team 1 would have won holes 1 & 3 which means they win the 3-hole match and get 1 point. However, if the formula is based off of the strokes, both teams totals are 16, resulting in a tie per the calculations. So then I created another column for each hole which I would later hide with the formula =IFS((D4)>(D7),"0", (D4)<(D7),"1", (D4)=(D7),".5"), then I had the formula =IFS(SUM(E4+G4+I4)>SUM(E7+G7+I7),"1",SUM(E4+G4+I4)<SUM(E7+G7+I7),"0",SUM(E4+G4+I4)=SUM(E7+G7+I7),".5") in the final "POINTS" column which would add the value of the hidden cells. This eliminated the calculated sum of strokes and now see whether or not each hole was either a W, L, or T. The problem is that I cannot get either formula to return a BLANK when no scores have been recorded yet. So basically a blank scorecard is resulting in a tie for each team and returning 1.5 points for each team. You will see that I have what appears to be 3 groups of teams but this is only because one of the teams may have an additional player which means each player on that team has to rotate through the matches. If I can figure out how to avoid the blanks, this will solve everything. Along with these formulas, I have added CF to make the team 1 scores Green for Win, Red for loss, and Yellow for Tie. The first image is my original spreadsheet before adding the column to be hidden and you can see in match 2 the reason for creating the additional column. The second image is my new sheet with the added column to be hidden. In both cases I could not figure out how to ignore the blanks.
This is probably simple, but I cannot figure it out. Maybe using the wrong formula to begin with?
Google Sheets: Sign-in
This is probably simple, but I cannot figure it out. Maybe using the wrong formula to begin with?
