Forum Discussion

Manda855's avatar
Manda855
Copper Contributor
Feb 10, 2023

Average of a range

Is it possible to enter a formula to calculate the average (excluding zero/blanks) and make sure there are a certain number of values used.

 

i.e. a sheet contains 104 cells with values, some of the values are zero and we don't want to include these, but we need the average of the first 52 cells that are not zero. 

Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 10, 2023

    Manda855 

    =AVERAGE(SMALL(IF((A1:A104<>"")*(A1:A104<>0),A1:A104),ROW(1:52)))

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

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 10, 2023

    Manda855 

     

    Try this one.

     

    Numbers is referring to A1:A104

     

     

    =LET(nozero,FILTER(numbers,numbers<>0),AVERAGE(TAKE(nozero,52)))
    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      Feb 10, 2023

      Manda855 

      You don't need the query for "" in Filter because you can exclude empty cells. If you can't use the TAKE() command, it also works like this:

      =AVERAGE(INDEX(FILTER(A1:A104,A1:A104<>0,""),SEQUENCE(52)))

       

Resources