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

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

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.

Hi,

I'm trying to build a statistical golf sheet. From what I found on the forum it was more leaderboard sheets.

On one tab I have data for the courses. Upon entering number of shots I managed using conditional formatting like ``Please Login or Register  to view this content.``
,but I'm having some issues with other parts of the spreadsheet.

a) On getting "course par" I want it to only count the number of holes I've played (in case I abort the round due to rain)
When I couldn't get it to work I was so frustrated I did the looong and messy ``Please Login or Register  to view this content.``
to cover all 18 holes, but I assume there has to be a better way to complete the task. Any help would be appreciated!
Edit2: I tried ``Please Login or Register  to view this content.``
, but there is something wrong with how I try to use it :/

b) For "FW" (fairway) I tried something like ``Please Login or Register  to view this content.``
, but it didn't work at all. I'm trying to look through the sheet with the course data and only count the number of holes that have par 4 or higher, but only for the holes I've played.

c) Calculating GIR (Green in regulation) averages.
I started trying something like ``Please Login or Register  to view this content.``
to check if anything was entered in the cells in range AC and fetch the value in range AB, but that didn't work.
Since I wasn't calculating these from the beginning I only want to calculate an average for the rounds where I gathered the data and only for that holes I played.

d) To calculate the average for FW I'm thinking I could just manipulate the formula for GIR averages.

e) Calculating birdies
When I googled I understood you couldn't use the colour code to count the number of cells with a particular colour if they were coloured by conditional formatting so I really don't have any idea how to do that in a non-messy fashion :/

Any helpful ideas on how to straighten the question mark into exclamation marks?

Best Regards,
Vigfus  Register To Reply

2. ## Re: 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?  Register To Reply

3. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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.  Register To Reply

4. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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   Register To Reply

5. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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.  Register To Reply

6. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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.  Register To Reply

7. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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!  Register To Reply

8. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

Well - checking this again I figured out that the formula was not summing up non-consecutive holes correctly.
The fact that you got the right result in your testings was by chance as many of cells do not have a unique value.
The formula summed hole 1 : last hole that is not blank in a sequence.

The good news is that I came up with a corrected formula Please try this in H30 and drag up till H14` ``Please Login or Register  to view this content.``  Register To Reply

9. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

Regarding section b: ``Please Login or Register  to view this content.``  Register To Reply

10. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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.  Register To Reply

11. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

A birdie is one under par, I believe.  Register To Reply

12. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

Thanks for the update belinda!
There seems to be an issue with the updated formula for a) ``Please Login or Register  to view this content.``
To me the new formula sums it up to 0 in cell H30.

I'm also getting 0 for the formula for b) in cell AF31. ``Please Login or Register  to view this content.``
For c) in cell S3 I'm getting #value for the calculation and I've tested with format cells to general as well as numbers (numbers made more sense to me since golf is about numbers so I left it at that). I've attached a picture from the Show calculation steps to evaluate the formula.
golf 2020_draft_version3_pic1.PNG

For e) AliGW is right, the conditions to be met for a Birdie is that the score on the hole is 1 under par (like in cell N26 when I only used 2 shots on a par 3 hole).

I've attached the latest version of the spreadsheet to this message so we don't mix the rows by accident as I've added some data for testing purposes.  Register To Reply

13. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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.  Register To Reply

14. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

H14
=IF(D14="";"";SUMIFS(INDEX(CourseData!\$E\$2:\$V\$20;MATCH(D14;CourseData!\$A\$2:\$A\$20;););J14:AA14;">0"))

AF14
=IF(D14="";"";COUNTIFS(INDEX(CourseData!\$E\$2:\$V\$20;MATCH(D14;CourseData!\$A\$2:\$A\$20;););">3";J14:AA14;">0"))

S3
=SUM(AC14:AC65)/SUMIFS(AB14:AB65;AC14:AC65;">0")

U3
=SUM(AE14:AE65)/SUMIFS(AF14:AF65;AE14:AE65;">0")

X2
=COUNT(1/(INDEX(CourseData!\$A\$2:\$V\$21;N(IF(1;MATCH(\$D\$14:\$D\$40;CourseData!\$A\$2:\$A\$20;)));N(IF(1;COLUMN(CourseData!\$E\$2:\$V\$2))))-1=\$J\$14:\$AA\$40))  Register To Reply

15. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

Nice Bo_Ry, I feel like saying how didnt I think of that...?

vigfus - my solution for your (e) is to add a column to search for birdies in each row, and sum them up in X2

AB14 and down (an array formula): ``Please Login or Register  to view this content.``  Register To Reply

16. ## Re: Combining COUNTIF with LOOKUP and counting averages where data might not exist

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!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 