AverageiIf for values between a set range

[IGoCougsI]

I've appreciated all the help I have received in the past on this forum. Looking for help once again!

I have data of golf shots. I want to report on the average divergence from a target. I don't know how to use AverageIf for an entire dataset when limiting the average to a range between two values (one equal or greater and the other less than).

In the attached spreadsheet I am looking to solve for the average left/right for a particular golf shot given a target distance. For example, row 14 has a golf shot target that is 52 yards (A14). Column B indicates the left or right variance of the shot. There are a series of shots within the cohort from =>50 yards to <55 yards. If I average their variance it comes out to 1.2 yards. (But, I had to do that manually.

Any help is greatly appreciated. With that formula determined I can then apply it to the other cells in the report located in cells A1 to H11.

Thanks!

[etaf]

does this work
=AVERAGEIFS(B14:B33,A14:A33,">="&50,A14:A33,"<"&55)
or using the cell values
=AVERAGEIFS($B$14:$B$33,$A$14:$A$33,">="&A2,$A$14:$A$33,"<"&B2)
get a couple of div errors as the values do not exist
what do you want to show then
use an IFERROR(AVERAGEIFS($B$14:$B$33,$A$14:$A$33,">="&A2,$A$14:$A$33,"<"&B2),"what to put here")

see column I

[AliGW]

Are you still using Excel 2010? If not, please update your forum profile.

[IGoCougsI]

Thank you. I have added a new value, Average Yards from Pin. The issue with this is that when I do the AverageIf...it gets skewed by the presence of 0.0 in column D.

The issue with the highlighted is that it includes the "0.0" values associated with rows 28-33 where there is no data. When no data, the cell calculates to 0.0 and thus is incorporated within the average. How to exclude rows when there is null values in column B and C?

See attached.

[DJunqueira]

Hehe, there is no Excel 2017.
It is important that you show the right version for us to give you the best answer.

[IGoCougsI]

Doh! I see now how to look it up. I’m in Excel for MS 365.

[DJunqueira]

You could try this.

Please Login or Register  to view this content.

This formula could be simplified too.

Please Login or Register  to view this content.

[etaf]

how about
=IFERROR(AVERAGEIFS($D$14:$D$33,$A$14:$A$33,">="&A2,$A$14:$A$33,"<"&B2,$D$14:$D$33,">"&0),"No Data")

which excludes zeros in column D , assuming zero is never a valid amount


=IFERROR(AVERAGEIFS($D$14:$D$33,$A$14:$A$33,">="&A2,$A$14:$A$33,"<"&B2,$C$14:$C$33,"<>"),"No Data")
That checks column C is not blank

BUT you say

How to exclude rows when there is null values in column B and C?

you say AND will a blank be in both B and C or could B just be blank and you do not want to calculate that row and then another row just C row blank

can you give an example
you can just exclude zeros in column D - but will there be zeros which are valid and need to be included
=IFERROR(AVERAGEIFS($D$14:$D$33,$A$14:$A$33,">="&A2,$A$14:$A$33,"<"&B2,$D$14:$D$33,">"&0),"No Data")