Golf Testing Scoring System

[DirkDirk]

Hi Community, 

Question about conditional scoring and sums. 

1. Golf players have 10 shots from different distances and score between 1-6 points depending on how close they get the ball. 
2. I would like to be able to enter the distance (feet) for each of the ten shots 
3. Then a score is allocated to each entry based on the distance in feet
4. Then to total score (sum of 10 shots at each distance) is used to create a chart 

Current... 

I have created a table that can collect the data and then SUM total into virtual columns and use those columns to create a chart. 

I have also got this for the conditional scoring.. 

IFS(
AND([50 Shot 1] >= 9, [50 Shot 1] <= 15), 1,
AND([50 Shot 1] >= 4, [50 Shot 1] <= 8), 2,
AND([50 Shot 1] >= 0.1, [50 Shot 1] <= 3), 4,
[50 Shot 1] = 0, 6
)

The Problem... 

How do I conditional scoring first? 

Thanks all, 

Phil 

[Suvrutt_Gurjar]

---

@DirkDirk wrote:

The Problem... 

How do I conditional scoring first? 

---

Could you elaborate? 

[DirkDirk]

Hi, 

I think what I'm asking is ... 

I'm unsure where to use the above expression. 

I'm thinking I'll need to have a virtual column to create the score then another virtual column to SUM those.  

Thank you. 

[Suvrutt_Gurjar]

In general, I believe your thinking is correct, assuming there is one record for each of those distance records, So a scoring VC calculates the score for each VC and then it needs to be added up. Instead of adding through an AppSheet VC that can be sync time expensive you could evaluate using a "group by" and then Sum option ins summary views.

[DirkDirk]

Thank you very much! 

I have tried a virtual column with the above expression to gain a "score" from the user input - then another virtual column to SUM the "scores" (there are 10 scores per distance).

However this does mean there will be 60 real columns then 60 virtual columns.

Can you explain the "group by"? Do you mean "group by" that is found when grouping data within a cards view? 

Thank you

Phil 

[Suvrutt_Gurjar]

---

@DirkDirk wrote:

However this does mean there will be 60 real columns then 60 virtual columns

---

You may want to elaborate this statement. A screenshot of table columns will help. It is not clear why so many real / virtual columns will be required.

If there is one distance in one record, only one column should to to compute the score for it?

https://support.google.com/area120-tables/answer/9902873?hl=en&sjid=3327532115003784057-AP

https://support.google.com/appsheet/answer/10106698?hl=en&sjid=3327532115003784057-AP

[DirkDirk]

Hopefully this explains why so many columns... 

Golfers get 10 shots for each of the 6 distances, therefore 6 scores to add to create total score for each distance. 

Hope this explains.

Thank you very much! 

Phil 

[DTFCM]

Try to create conditional formats with a calculation column that translates the condition into a number value, with this, in the equation you have in mind you solve it with a sum (selectc...

[DirkDirk]

Sorry, I missed some info. 

Golfers get 10 shots for each of the 6 distances, therefore 6 scores to add to create total score for each distance. 

Hope this explains.

Thank you very much! 

Phil 

[Suvrutt_Gurjar]

---

@DirkDirk wrote:

Golfers get 10 shots for each of the 6 distances, therefore 6 scores to add to create total score for each distance. 

---

In general if there are 60 readings yes, somewhere 60 scores will need to be computed.

There could be a few options in the data schema such as one record of 10 shots per distance, so 6 records for a golfer for 6 distances.

or 1 record per shot so 60 records

Another option if all the 60 shots are recorded in the same record is to compute the score for each shot in a single VC and even sum  it in the same vc. However for such an approach, the expression to compute score will be very lengthy and unwieldy. If there are 6 records ( one per distance) per golfer then the score will need to be added for 10 shots in a record.

Yet another option can be , for each record of 60 distance readings, one adds a record through action in a "Scores" table. However this will need another action to modify the scores whenever changes happen in the main shots logging table.

Please do note that these are all design related approaches and please thoroughly evaluate the best options in the long run and considering overall app design you have.  The community may or may not be able to suggest if you take a certain approach and face any issues later because all these inputs are based on the brief details shared. Please take these as general guiding inputs only.

[DirkDirk]

Thank you so much for your detailed reply. 

I've gone down the route of adding a VC to calculate the score for each 10 shots for each distance then another VC to SUM those scores. 

There are 60 VCs then an additional 6 VCs for each distance. It's working.... so far.....

Thank you and I appreciate your time. 

Phil 

[AleksiAlkio]

Why do you need 60 virtual columns? What is the goal for these.. I mean what are you going to do with those?

[AleksiAlkio]

Are you filling the distance from the "flag", or the overall distance? How accurate do you measure it?

[DirkDirk]

Hi Aleksi

The coach would fill in the distance from the flag for each shot taken. The students has 10 shots from 8 different distances and all are recorded and can score points. 

In terms of accuracy, the distance is rounded up. The bands are: 

• 6 points for getting the ball in 
• 4 points for inside 3 feet
• 2 points for >3 to 8 feet 
• 1 point for >8 to 14 feet 

Cheers.

 

[AleksiAlkio]

With Enum/Ref column, the result would be something like this.. and the Score is calculated exactly like [Pitch 1]+[Pitch 2]+[Pitch 3], no virtual or other columns are needed. In this sample, the Pitch 1 = "Ball in" and these numbers are distances (feet) from the flag.

[DirkDirk]

Thank you and I really appreciate your time.

I can't get my head around this idea, I must be missing something or simply don't have the knowledge to understand it. Sorry. 

Would this allow the coach to input the distance from the flag? Each person who does the test has 60 shots each test in total. 

[AleksiAlkio]

If the accuracy is one feet, and it's enough, you would not need more than these.. or maybe you could use "Ball in hole" and "Too far away".

[AleksiAlkio]

Meaning.. you would need to have 60 of these Enum columns + 6 normal columns where those 6 sets are counted.

[AleksiAlkio]

You could have 6x10 columns like this.. and they are all reading values from one Score table.

The magic is happening in that Score table. It has two columns, Distance and Point. The Point is the key column and Distance is the label. Because the key column needs to be unique, you can't have the same points for multiple rows, and that's why you need to use decimal values that are close to your real values. When you sum them with the number column (Score), those decimals doesn't matter.. If you would need to count all these 60 pitches together, then you need to use 1.0001 instead of 1.01 etc.

[DirkDirk]

I am very grateful for your time and expertise.  I am going to give this a go. Currently I have something working, however it does have a lot of VCs. 

Thank you again and I hope this thread is not taking up too much of your time. 

[AleksiAlkio]

If you would build the app with 10+1 columns, it would be easier for coaches to handle the data with the mobile device. You could then build a view like this and the app user would not need to scroll 60+6 columns left and right.

[AleksiAlkio]

What I have used with other score apps, I have splitted the tests to 6 different rows. Then you would only need 10 + 1 normal columns in your table, not 60 + 6. Then each players would have 6 different tests from 6 different distances.

[DirkDirk]

Hi, 

Sorry for the delay in my response. 

Little update. I've gone ahead with creating a VC for each shot (60) plus 6 more to SUM those totals to then use to create a graph. 

Seems to work fine and the users are happy. I understand it's probably not as streamline as it could be.  

[AleksiAlkio]

What I'm thinking here.. create an another table with two columns.. Score (key) and distance (label).

Set the scores like 1.001, 1.002, 1.003, 2.001, 2.002 etc. for different feets. Then you can show the feet for the user with the Enum column base of Ref, but the score is saved to your data source. Then you don't need to use any virtual columns.. and those 6 summary columns can be normal columns as well.

[DirkDirk]

Just wanted to say THANK YOU for all the help sent on this topic. 

I genuinely appreciate the time and effort given. 

Thank you. 

[AleksiAlkio]

You're welcome! It was an interesting case like how to do it with a simple way.