Are you struggling to find a way to calculate the average across rows in Google Sheets? Look no further! In this article, I will show you the best methods to achieve this.
Why doesn’t the Average function expand its results?
The AVERAGE function in Google Sheets doesn’t expand its results; it only returns a single number to represent the range or array used. This can be frustrating if you need to calculate the average for each row.
So, how can we solve this problem? Let’s explore some alternative solutions.
Average Array Formula Using MMULT – Regular Method
One popular method is to use the MMULT function in Google Sheets. To calculate the average across rows, we’ll use two MMULT formulas – one for summing the values and another for counting the rows. By dividing the sum by the count, we obtain the average array result across rows.
Here is the formula to calculate the average, excluding zero:
=ArrayFormula(mmult(N(array_constrain(B2:D,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)/mmult(N(array_constrain(if(B2:D>0,1,0),MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0))And if you want to include zero in the calculation, simply replace if(B2:D>0,1,0) with if(len(B2:D),1,0) in the formula.
Query and DAVERAGE Alternative Solutions – Regular Method
Another option is to use the QUERY or DAVERAGE function in Google Sheets. While the QUERY formula is straightforward, it has some limitations. It includes zeros in the calculation and doesn’t return results for rows with blank cells.
Here is a sample QUERY formula:
=query(A1:D,"Select (B+C+D)/3")For a more advanced solution that handles blanks and includes/excludes zeros, you can check out the article “Average Each Row in Dynamic Range in Google Sheets.”
Average Array Formula Using BYROW LHF – New Method
Now, there’s a new and easy way to expand the AVERAGE formula using the BYROW Lambda Helper Function (LHF). This method allows you to include or exclude zeros in the calculation.
To calculate the average, including zero, use the following formula:
={"Average Including Zero";byrow(B2:D,lambda(row,iferror(average(row))))}To exclude zero in the BYROW average array formula, replace average(row) with averageif(row,">0").
And that’s it! Now you can easily calculate the average array across rows in Google Sheets using these efficient methods.
If you want to learn more about Google Sheets and other useful formulas, make sure to visit Crawlan.com. Happy calculating!
