Combining COUNTIF with LOOKUP and counting averages where data might not exist

Originally Posted by vigfus

Edit: I highlighted the cells in the attachment with yellow where I'm struggling. The question related to the cells are a): H30, b): AF30, c) S3, d) U3, e) X2.

What are the values you are expecting to see in each of these cells and why?

a)
Expected value: Par for the holes i played on that round.
Expected value in data: H30 is played on Bromma and only holes 1-9 have been played. In CourseData we count the par for the corresponding holes. Thus expected value would be 29.
Why: In golf everything is derived from how well you do on a hole compared to the par for the hole.

b)
Expected value: The total number of played holes that have "par > 3".
Expected value in data: AF30 is played on Bromma and only holes 1-9 have been played. In CourseData only two corresponding holes have par > 3. Thus expected value would be 2.
Why: On longer holes you want to know how well you do from tee and that is done by counting how many FW you hit out of the total number of FW.

c)
Expected value: Adding all values in column AC and divide by the number of holes in column AB, but only for those rounds that have a value > 0 in column AC. Since I already rounded column AD to two decimals I didn't want to use that column for the calculation.
Expected value in data: As only one entry is found in column AC the expected value atm would be 2/9 = 0,22222...
Why: To get a feel for how often you manage to hit the green within the same number of shots as the pros.

d) I can derive that one from the formula for c)

e)
Expected value: A number that is derived from all cells in the "Holes played matrix" that are light blue as that indicates a Birdie (one shot under par for the hole).
Expected value in data: Atm there are four holes in the matrix that are light blue (R30 is dark blue and should not be counted). Thus expected value would be 4.
Why: To get a feel for how often you manage to do better than the pros. Atm I'm not good enough to go two shots under par and if that miraculously happens due to blind luck I can create a new box for what in golf term is called an Eagle and use the same calculation as for Birdies.

Hi,

So - Thanks for broadening you explanations. It helped very much except section (e):

a. J31-R31 as helper row to retrieve the value in 'CourseData'
=INDEX(CourseData!$A$1:$V$3, MATCH('2020'!$D$30,CourseData!$A$1:$A$3,0), MATCH("Par"&" "&J13,CourseData!$A$1:$V$1,0))

H30 =SUM(J31:R31)

b. AF30 =COUNTIF(OFFSET(CourseData!A1,MATCH($D$30,CourseData!$A$1:$A$3,0)-1,4,1,'2020'!$AB$14),">=4")

c. S3 =SUMPRODUCT(IF(AC14:AC31<>"",AC14:AC31))/$AB$14

d. You didnt any help with

e. still dont understand...where is the matrix you mention and why would the result be 4?
Please refer to the exact cell/s you are erefering to and what is the calculation you do to get to "4".

Why: To get a feel for how often you manage to do better than the pros. Atm I'm not good enough to go two shots under par and if that miraculously happens due to blind luck I can create a new box for what in golf term is called an Eagle and use the same calculation as for Birdies.

When I asked why, it was not in order to understand the Golf game or your motive to get this info. You have evey right to look for any data you like, I was trying to understand your excel file in order to help to retrive this data correctly

Thanks for the help belinda and sorry, my bad for explaining poorly.

It didn't seem like the formulas worked. To hopefully make it easier to understand what I'm trying to do I've added data for two more golf rounds and uploaded a version2 of the spreadsheet in this message (and in the original one) in an attempt to make it clearer.
On row 28 only 11 holes were played on that day and on row 29 a full round of 18 holes were played to hopefully give you a better understand of what I'm trying to achieve (at least in my head).

What I expect the user to do to make the spreadsheet and its formulas dynamic is to enter data in the white fields - especially in column D as that is they key to gather data on the sheet 'CourseData'.
Gray background in a cell means I want a formula to calculate the data. I noticed I missed the grey background on some cells in the original spreadsheet and corrected that in version 2.

a)
I don't understand how the formulas for INDEX and MATCH help us gather and sum up the data when i google the uses for them.
For the value in 2020!H28 I'm trying to use the value in 2020!D28 (I'm thinking of something like "LOOKUP(D$28..." to look for the appropriate row in CourseData!$A:$A). In this case it finds "Nacka" on row 4.
Since I only entered data in cells '2020'!J28:T28 I only want to fetch data for those corresponding holes (sum of E4:O4 in sheet CourseData) and thus give us the value 45 in 2020!H28.
If the user then adds another result in 2020!V28 the value in H28 should be updated to calculate the corresponding holes (sum of (E4:O4 + Q4) in sheet CourseData) and thus give us the value 48 in 2020!H28.
If the user then changes the value in the 2020!D28 to Bromma then the value in 2020!H28 should change. The LOOKUP-function would find "Bromma" on row 2 in sheet CourseData and then calculate the sum in (E2:O2 + Q2) and thus give us the value 38 in 2020!H28.

b)
For the value in 2020!AF28 it's similar to a) with the LOOKUP, but instead of SUM to get the total value I assume it would be some sort of COUNTIF for when the corresponding holes on sheet CourseData have par > 3.
To give a sense as to what I'm looking for I entered values by hand in some fields
AF26 should look in CourseData row 2 since cell D26 holds the value 'Bromma' and hold value 2 since there are only 2 holes in range CourseData!E2:M2 (I played 9 holes) that have par > 3.
AF29 should look in CourseData row 4 since cell D29 holds the value 'Nacka' and hold value 14 since there are 14 holes in range CourseData!E4:V4 (I played 18 holes) that have par > 3.
AF28 should look in CourseData row 4 since cell D28 holds the value 'Nacka' and hold value 9 since there are 9 holes in range CourseData!E4:O44 (I played 11 holes) that have par > 3.
If the user then adds another result in 2020!V28 the value in H28 should still hold value 9 since hole Q4 is a par 3.

c)
In the spreadsheet version 2 there are 3 rounds when I have calculated GIR.
To get those numbers I look for cells that have a number (maybe less error prone if I make the cells in AC14:AC62 use a drop down with values 'Blank', 0, 1, 2,..., 17, 18) in range AC14:AC62.
Each cell that has a value (between or equal to 0-18) in AC14:AC62 is should be added to the dividend and the corresponding cell values (AB26 for AC26 and so on) should be added to the divisor.
In 2020!S3 the value would be calculated by ((AC26 + AC28 + AC29)/(AB26 + AB28 + AB29)) or ((2+4+5)/(9+11+18))in version 2 of my spreadsheet.
If the user then adds another result in 2020!V28 the value in AB28 would rise to 12 as another hole was played and assuming AC28 wasn't changed S3 would then be calculated like ((2+4+5)/(9+12+18)).

d)
skipping this one

e)
In the spreadsheet version 2 there are 5 cells in the score matrix (J14:AA62) that displays a Birdie was made. Those cells are N26, R26, O28, N30 and O30 (displayed in light blue).
I guess we could check each cell in the score matrix (J14:AA62), but I was hoping with the colour system we could calculate it in an easier fashion.

OK I think I got it at least for the 'a' section.

I applied a new formula in H30, which you can drag up to H14:
=SUM(OFFSET(CourseData!$A$1,MATCH('2020'!D30,CourseData!$A$1:$A$4,0)-1,4,,COUNTIF('2020'!$J30:$AA30,"<>")))

Please confirm this is what you meant.

Thanks.

I've toggled between all the courses. I've changed data in the score matrix (J14:AA62) by adding both consecutive and non-consecutive holes to a round and I've changed the par for the course in the CourseData sheet and everything works like a charm.

Thank you belinda!

Regarding section b:

Please use below in AF29:

Please Login or Register  to view this content.

For (c) in S3 it would have to be :

S3=SUMPRODUCT(($AC$14:$AC$30)*($AC$14:$AC$30<>""))/SUM(IF($AC$14:$AC$30<>"",$AB$14:$AB$30))

Now for (e) -

e)

In the spreadsheet version 2 there are 5 cells in the score matrix (J14:AA62) that displays a Birdie was made. Those cells are N26, R26, O28, N30 and O30 (displayed in light blue).

What conditions should be met in order to have 'birdie'? I need to know in order to make a rule that would find these cells and count them.

Thanks.

Hi vigfus,

All these formulas are an array type, which means that in order to work correctly, it needs to be entered with control + shift + enter. When you enter a formula this way, you'll see the formula wrapped in curly braces {} in the formula bar.

A big thank you belinda for all the hard work and thank you Bo_Ry for helping out!

Now I can focus on being frustrated over my golf game rather than my Excel skills

Stay safe out there!

Hi,
This is a continuation of the work above so I figured it was better to keep it in the same thread.

During the summer I noticed some things were odd in the attached spreadsheet so I tried to figure out what, but can't come to any conclusion.
The conditional formatting is done cell by cell for row 14 and then I've used Format Painter one row at a time from row 14 to row 150.

The issues I have are the following (you see them in column AC)
a) Some golf rounds get compared to the wrong course in the tab CourseData (blue highlight)
b) Some golf rounds get compared to the last course in the tab CourseData (yellow highlight)
c) Some golf rounds doesn't get compared at all (orange highlight)
d) Some golf rounds gets compared to the wrong course in the tab CourseData AND can't be found in the tab CourseData - that's what I'm assuming at least (pink highlight)
e) One golf course can't be found in the tab CourseData, but gets compared to the right course in the tab CourseData (redish highlight)

I removed Swedish characters if they were affecting somehow, but that didn't seem to fix anything.

Can anyone understand what kind of problem I have run into?

Best Regards,
Vigfus