New Contributor

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.

4 Replies

Re: Average of a range

Try this one.

Numbers is referring to A1:A104

``=LET(nozero,FILTER(numbers,numbers<>0),AVERAGE(TAKE(nozero,52)))``

Re: Average of a range

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

Re: Average of a range

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

Re: Average of a range

Fabulous you are all fantastic