How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Wednesday

Average Each Row in Dynamic Range in Google Sheets

Posted by   on

We can use the DAVERAGE function in a most unusual way to average each row in a dynamic range in Google Sheets.


The above function falls under the Database category, which usually requires structured data. But even if the data is not structured, we can use it. I’ll explain how at a later stage of this tutorial.


When it comes to getting the row-wise average, no doubt Query and MMULT are the time-tested functions to use in Google Sheets. I have already experimented with it and detailed it here – Average Array Formula Across Rows in Google Sheets.


By using DAVERAGE for getting the mean value of each row in Google Sheets, let’s be a little different from the rest of the pack.


I will show you how to use the DAVERAGE in Google Sheets to average each row in a dynamic range.


Dynamic range here means the range that is open both horizontally and vertically.


Mean Value of Each Row in Google Sheets


There are two main obstacles that we may face when using the DAVERAGE to get the average/mean value of each row in a dynamic range.



  1. It requires structured data.

  2. The next is not a DAVERAGE issue. It is one of the most common average problems in Spreadsheets, i.e., to skip/avoid cells containing blank or zero in the calculation.


We can address both of them.


Average Each Row Including Zero Values in a Dynamic Range


Formula and Example (Formula#1):


=ArrayFormula( IFERROR( daverage( transpose(A3:A,indirect("C3:"&rows(A:A))), sequence(rows(A3:A),1), if(,,);if(,,) ) )
)

Average Each Row in Dynamic Range - Zero Included

The above formula is equal to using =iferror(average(C3:N3)) in B3 and dragging down until the row that you want. But here, you need to insert the formula in the top row, i.e., in cell B3. It will expand down.


The above DAVERAGE covers all the rows in the sheet from row#3 onwards and columns to its right from column C.


You can add more columns and rows to the Sheet. The formula will automatically include that rows and columns in the row-wise average calculation.


So above is one of the best ways to average each row in a dynamic range in Google Sheets.


Note:- If you face performance issues, delete unwanted/unused rows from the bottom and columns from the right.


Logic


Formula Syntax: DAVERAGE(database, field, criteria)


The DAVERAGE is capable of returning the average in each column in unstructured data as below.


DAVERAGE Each Column

I know I must explain it before proceeding further.


Since there are no field labels in the data range D4:E6, used the database as D4:E4;D4:E6 so DAVERAGE will treat D4:E4 as the field labels.


It doesn’t matter whether D4:E4 has values or not. The DAVERAGE needs one row above the database D4:E6 to treat as field labels.


The formula returned the mean values of the columns in the database individually in D8 and E8. It is because we have specified the field as 1,2.


The last part of the formula, i.e., if(,,);if(,,), is to specify that there are no criteria to consider.


Formula Explanation


The above database function is not capable of returning the average in each row in a dynamic range. But, as you have already seen above, it works well with columns in Google Sheets.


So in our original example, we have transposed the database. The rows became columns. In that, we can apply the above logic. It’s as follows.


Database: transpose(A3:A,indirect("C3:"&rows(A:A)))



  • Here A3:A acts as the field labels. You can use this range even if it is blank.

  • The formula part indirect("C3:"&rows(A:A)) is our original data range (database) and the essence of dynamic range. For example, if you have 1000 rows in your sheet, the Indirect formula would return C3:1000. That means all rows and columns.


Field: sequence(rows(A3:A),1)


Instead of specifying 1, 2, 3, I have automated it to cover all the rows using the Sequence function.


Criteria: if(,,);if(,,)


No criteria.


We can use the above formula to mean/average each row in a dynamic range, including zero in Google Sheets. The below example shows how to exclude zeros.


Average Each Row Excluding Zero Values in a Dynamic Range


In the above same example, if you want to skip the cells containing zeros in the row-wise mean calculation, use this one.


Formula#2:


=ArrayFormula( IFERROR( daverage( transpose(A3:A,if(indirect("C3:"&rows(A:A))>0, indirect("C3:"&rows(A:A)),)), sequence(rows(A3:A)), if(,,);if(,,) ) )
)

Average Each Row in Dynamic Range - Zero Excluded

The above formula is equal to using the below AVERAGEIFS in cell B3 and dragging down.


=AVERAGEIFS(B3:N3,B3:N3,"<>0")

If you compare formula#2 with formula#1, the only change is in the database part of the formula.


Here used an IF logic to return only the values greater than zero. In other words, all the zeros are replaced by blanks.


Conclusion


The database functions in Google Sheets are fully flexible because we can create virtual arrays using Curly Braces and use them in it. So you may not be able to replicate the above output in Excel using the DAVERAGE.


If you face any errors when applying my formula in your Sheet, please copy my sample sheet from the link given below. Then go to the menu File > Spreadsheet settings, change the Locale and Time zone to yours.


Sample_Sheet_16621


Then compare your formula with mine. That could help you solve the issue.


If that doesn’t solve the problem, please drop it in the comments below.


I have similar tutorials using other database functions. Please go through the resources below.


That’s all about average each row in a dynamic range in Google Sheets. Thanks for the stay. Enjoy!


Resources:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!