Average of a range

[Manda855]

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. 

[#2]

@Manda855 

Try this one.

Numbers is referring to A1:A104

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

[#3]

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

[#4]

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

 

[#5]

Fabulous you are all fantastic