Forum Discussion

TylerUnderscore's avatar
TylerUnderscore
Copper Contributor
Jun 06, 2023

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.

 

 

 

excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jun 06, 2023

    TylerUnderscore 

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

     

    Perhaps this:

     

     

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

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      Jun 06, 2023

      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")
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 06, 2023

    TylerUnderscore 

    =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.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 06, 2023
    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)))

Resources