Help needed: Average of every third cell; including zeros but ignoring blanks

[Tamarama91]

Hello!
I need to find the average of every third cell in a certain range. Until now, I used
=AVERAGE(IF(MOD(COLUMN(BR24:CP24)-COLUMN(BR24),3)=0,IF(BR24:CP24,BR24:CP24))) ( so I need to count the values in BR24, BU24, BX24 and so on).
which worked just fine.
However, I just realized that I have to include cells containing a "0" (while at the same time ignoring blank cells) as I'm working with test results.
Any suggestions on how to do this?

I also need to exclude the lowest value (only one, not all of them) - I might be able to figure that out myself afterwards, but if you already know how to do this either, I'd appreciate your help.
Thanks in advance!

 

[sheetspread]

I would add a helper column, put all the conditions there, and then averageif. A single formula is possible but far too cluttered.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AVERAGE(FILTER(BR24:CP24,(MOD(COLUMN(BR24:CP24)-COLUMN(BR24),3)=0)))

 

[Tamarama91]

I would add a helper column, put all the conditions there, and then averageif. A single formula is possible but far too cluttered.

Thank you - haven't tried that before (I'm pretty much a newbie to Excel and while I'm able to find most solutions by googling+logical thinking, I still have to learn A LOT). Fluff's answer worked for me though, but I'll look into your recommendation for the next time!

 

[Tamarama91]

Hi & welcome to MrExcel.
How about

=AVERAGE(FILTER(BR24:CP24,(MOD(COLUMN(BR24:CP24)-COLUMN(BR24),3)=0)))

Works like a charm. Thank you a lot for your quick help!

 

[Fluff]

You're welcome & thanks for the feedback.