SOLVED

Proximity Tracker

%3CLINGO-SUB%20id%3D%22lingo-sub-2200374%22%20slang%3D%22en-US%22%3EProximity%20Tracker%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2200374%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20a%20golfer%20and%20I'm%20trying%20to%20track%20the%20average%20distance%20to%20the%20hole%20sorted%20by%20the%20club.%20Basically%2C%20I%20think%20I%20need%20an%20equation%20that%20goes%20something%20like%3CSPAN%3E%2C%20if%20column%20S%20matches%20column%20H%2C%20in%20column%20T%20for%20it%20to%20show%20the%20average%20of%20column%20E%20when%20the%20value%20of%20column%20C%20is%201.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2200374%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2200440%22%20slang%3D%22en-US%22%3ERe%3A%20Proximity%20Tracker%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2200440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F993423%22%20target%3D%22_blank%22%3E%40riosborne%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20T3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(AVERAGEIFS(tblPutts%5BLength%5D%2CtblPutts%5BClub%20on%20approach%5D%2CS3%2CtblPutts%5BPutt%5D%2C1)%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2200465%22%20slang%3D%22en-US%22%3ERe%3A%20Proximity%20Tracker%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2200465%22%20slang%3D%22en-US%22%3EThank%20you%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I'm a golfer and I'm trying to track the average distance to the hole sorted by the club. Basically, I think I need an equation that goes something like, if column S matches column H, in column T for it to show the average of column E when the value of column C is 1.

9 Replies

@riosborne 

In T3:

 

=IFERROR(AVERAGEIFS(tblPutts[Length],tblPutts[Club on approach],S3,tblPutts[Putt],1),"")

 

Fill down.

Thank you

@Hans Vogelaar 

 

What if I wanted to track hit/missed greens?

 

See attached.

@riosborne 

Can you explain what exactly you want, for someone who doesn't know anything about golf?

Oops! I thought everyone played golf

I want column R to produce an average of columns I and H but only if the value in columns H or I are the same as column S. That make sense?

So if the number is column H it means I hit the green (positive). If the number is column I it means I missed the green (negative). I'd like column R to produce the average of those two columns when the value is the same as whats in column S.

I think I explained that correctly but lmk if its still confusing. Thank you so so much.

So if the

@riosborne Does the Putt column still come into this?

And do you mean the average of column E when I or H matches S? The average of I and H appears strange to me since they contain the values that occur in S...

@Hans Vogelaar 

I think we can ignore the putt column, but I may not be inputting values in a way that is possible to produce the desired outcome.  

 

What I want in column R is an average using columns H and I as "yes or no" values, respectively. For example in line 3, with the number 60 in column S, I want every time that the number 60 shows up in column I or H for it to produce a percentage where column H is a yes, and column I is a no. That possible?

 

Make sure you are looking at the most recently updated doc.

 

 

best response confirmed by riosborne (Occasional Contributor)
Solution

@riosborne 

 

Try this in R3:

 

=IFERROR(COUNTIF(tblPutts[Club on approach],S3)/COUNTIF(tblPutts[[Club on approach]:[Miss Green?]],S3),"")

 

Format R3 as a percentage, then fill down.

you're a genius! Thank you! Hans, I will take you golfing whenever you decide to take up the greatest game in the world