Formula range with gaps

Copper Contributor

Hi, I am trying to work out the average of the yellow cells but do not know how to specify a range of multiple cells; I also need the average to exclude zeros.





4 Replies
Hopefully you have Excel 365 because it would be easiest to use LET() and other 'new' functions something like:
=LET(in, M5:X5, vals, choosecols(in,sequence(columns(in)/4,,4,4)), average(if(vals=0,"",vals)))



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.


Edit: didn't see the <>0 part of the request. Formula revised.


Perhaps this:






@Patrick2788 that is probably the most straight forward but OP requested not =0 also so maybe: