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 th...
Formulas and Functions
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