+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    01-15-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    Question 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
    Attached Files Attached Files
    Last edited by vigfus; 05-22-2020 at 09:15 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

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

    Quote Originally Posted by vigfus View Post
    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?

  3. #3
    Registered User
    Join Date
    01-15-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    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.

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-15-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    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.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-15-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    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!

  8. #8
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

    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.
    Last edited by belinda200; 05-23-2020 at 02:19 PM.

  9. #9
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

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

    Regarding section b:

    Please use below in AF29:

    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

    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.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

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

    A birdie is one under par, I believe.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  12. #12
    Registered User
    Join Date
    01-15-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    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.
    Attached Files Attached Files
    Last edited by vigfus; 05-24-2020 at 10:37 AM.

  13. #13
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

    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.

  14. #14
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,099

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

    Please try at
    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))
    Attached Files Attached Files
    Last edited by Bo_Ry; 05-24-2020 at 02:33 PM. Reason: Add Birdies count

  15. #15
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    911

    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.
    Attached Files Attached Files
    Last edited by belinda200; 05-24-2020 at 01:55 PM. Reason: Adding a notice 'array formula'

  16. #16
    Registered User
    Join Date
    01-15-2019
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    8

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 11-12-2019, 08:55 AM
  2. [SOLVED] Lookup a table to see if data exist?
    By Dwexdwex in forum Excel General
    Replies: 1
    Last Post: 08-03-2013, 08:47 AM
  3. [SOLVED] Counting Data Which Doesn't Exist..
    By tommyfernandez in forum Excel General
    Replies: 8
    Last Post: 07-31-2013, 02:28 PM
  4. Combining an COUNTIF/SUMIF/other statement with a LOOKUP ?
    By bellevue in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-08-2012, 10:34 AM
  5. [SOLVED] Help with combining lookup and countif formula's
    By superskull1 in forum Excel General
    Replies: 9
    Last Post: 08-04-2012, 11:43 AM
  6. Replies: 0
    Last Post: 05-04-2011, 06:26 PM
  7. Combining LOOKUP and COUNTIF functions
    By kate_suzanne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2006, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1