Forum Discussion
Formula range with gaps
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.
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)))
=LET(in, M5:X5, vals, choosecols(in,sequence(columns(in)/4,,4,4)), average(if(vals=0,"",vals)))