Formula range with gaps

Copper Contributor

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.

4 Replies

Re: Formula range with gaps

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)))

Re: Formula range with gaps

``=AVERAGE(IF(MOD(COLUMN(B:L),4)=0,B2:L2))``

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

Re: Formula range with gaps

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

Perhaps this:

``=AVERAGEIFS(C2:I2,C2:I2,"<>0",C1:I1,"£/week")``

Re: Formula range with gaps

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

``=AVERAGEIFS(C2:I2,C\$1:I\$1,"£/week",C2:I2,"<>0")``