The AT_EACH_CHANGE Named Functions will help you insert the calculated output of summary functions at each group start row or end row in Google Sheets.
The summary functions are Sum, Count, Counta, Max, Min, Average, Product, StdDev (sample), StDevp (population), Var (sample), and Varp (population).
The function names will be SUM_AT_EACH_CHANGE, AVG_AT_EACH_CHANGE, MIN_AT_EACH_CHANGE, etc. and the syntax of all the functions will be the same.
For example, my =SUM_AT_EACH_CHANGE(A2:A13,B2:B13,1))
will place the subtotals in the first row of change in value.
On the contrary, the =SUM_AT_EACH_CHANGE(A2:A13,B2:B13,-1)
will place it in the last row.
AT_EACH_CHANGE Named Functions – Syntax and Arguments
Actually, the Named Functions are a blessing for novice Google Sheets users.
Because they don’t worry about modifying complex formulas found online and end up in parse errors.
Even the experts can save lots of time by using them.
We have started a new section for Named Functions on this site, where you can find a growing list of custom functions that you can directly import to your Sheet and use.
Let’s go to the syntaxes of the AT_EACH_CHANGE Named Functions.
Since we have a list of functions (scroll down to see it) with the same arguments here, let’s take one of them and learn it.
Syntax: SUM_AT_EACH_CHANGE(group_range, subtotal_range, mode)
group_range
: The range to test for value change. The functions require a sorted column.
subtotal_range
: The range to Sum, Count, Counta, Average, Max, Min, Product, StdDev, StdDevp, Var, or Varp.
mode
: In which row to insert the totals, in the first row (1) or end row (-1) of each group.
Usage Notes
- If your group range contains 100 rows, the formula requires an equal number of blank rows from the row it resides. Otherwise, it would return a #REF error.
- The
group_range
must not contain blank cells, which may cause #N/A errors in corresponding cells in the result column. - You can remove those errors by wrapping the formula with the IFNA function.
- The AT_EACH_CHANGE Named Functions support open ranges such as A2:A and B2:B.
List of All AT_EACH_CHANGE Named Functions and Examples
Here is the list of the AT_EACH_CHANGE custom Named Functions you can download from the sample sheet at the end. It all follows the above same syntax.
- SUM_AT_EACH_CHANGE – Sum
- COUNT_AT_EACH_CHANGE – Count
- COUNTA_AT_EACH_CHANGE – Counta
- AVG_AT_EACH_CHANGE – Average
- MAX_AT_EACH_CHANGE – Max
- MIN_AT_EACH_CHANGE – Min
- PRODUCT_AT_EACH_CHANGE – Product
- STDEVS_AT_EACH_CHANGE – StdDev (StdDev.S)
- STDEVP_AT_EACH_CHANGE – StdDevp (StdDev.P)
- VARS_AT_EACH_CHANGE – Var (Var.S)
- VARP_AT_EACH_CHANGE – Varp (Var.P)
Please scroll up and see images # 1 and 2 for examples.
Here are some of the formulas which are present in those examples.
Max at Each Group change in D2 (image # 2):
=MAX_AT_EACH_CHANGE(A2:A13,B2:B13,-1)
Min at Each Group change in E2 (image # 2):
=MIN_AT_EACH_CHANGE(A2:A13,B2:B13,-1)
Average at Each Group change in G2 (image # 2):
=AVG_AT_EACH_CHANGE(A2:A13,B2:B13,-1)
How Do I Import and Use It?
First of all, make a copy of my example Sheet.
Then open your Sheet and find Named Functions under the Data menu. Select it.
On the sidebar panel that opens, click Import and import the functions you want from my copied Sheet.
That’s all.
I request you share your experience using my AT_EACH_CHANGE Named Functions that may help me develop more such functions for Google Sheets users in the future.
Thanks for the stay. Enjoy!
Hi, cool custom function! Works well. I might suggest it would be more effective as a single function and a CHOOSE function that selects which of the 11 functions to use (same as the existing SUBTOTAL function).
Also, you might already know this, but for Google Sheet’s custom functions, you don’t have to use the outer LAMBDA wrapper as the custom function interface already does that. Just start with the BYCOL part and it is easier to enter and adapt later. 🙂
Thanks a ton for all your posts and tips.
Hi, McKay,
Thanks for your valuable feedback. I’ve taken note of it.
Hi Prashanth,
Thanks for the help.
I solved the problem using the Query function with Group and Pivot.
I put the notes of the periods in separate columns, and I used a formula from infoinspired.com to calculate the average, as you mentioned in a helper column.
Hi, Harvi,
Thanks for your feedback.
Hi Prashanth,
Thank you very much for AT_EACH_CHANGE Named Functions.
I work in a school, and I need to calculate the average of the students in several subjects over the three periods of the year.
It is like using average if set, where the criteria are the name of the student, the subject, the period, and the respective grade.
Hi, Harvi,
It might be possible. You must use a helper column to combine other criteria columns like =Arrayformula(A2:A&B2:B&C2:C) and use that in group_range.
I could help if you include an example sheet URL in your reply below.