However, the result needs to ignore any zeros that appear in the data.
This formula can be easily solved with theAVERAGEIF functionor theAVERAGEIFS function.
It can also be solved with a combination ofFILTERandAVERAGE.
See below for details.
you’re free to see the results from AVERAGE in column H of the worksheet shown.
An easy solution in this case is to switch to the AVERAGEIF or AVERAGEIFS function instead.
AVERAGEIF function
TheAVERAGEIF functioncalculates the average of the numbers in a range that meet a single condition.
This is the effect of excluding Quiz scores equal to zero.
AVERAGEIFS function
The AVERAGEIFS function works like AVERAGEIF, except it is designed to applymultiplecriteria.
With AVERAGEIF the average range is the last argument and is optional.
For more details on the syntax of AVERAGEIFS with many more examples,see this page.
This array is returned directly to the AVERAGE function, which calculates the average.
Zero values never make it into the AVERAGE function to start with.
For more on FILTER,see this page.
AVERAGE Function
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
