GroupBy
The GroupBy() function groups a range of data into sets and calculates values per set using aggregation functions, for example, SUM, AVERAGE, COUNT etc. GroupBy() is an aggregate-type function.
Related functions: Pivot
Syntax
GroupBy (Range, Group Columns, Formulas, Calculate Error Value)
| Range | The range of Excel cells used as input |
| Group Columns | The columns that the group function will group by, in the order specified, for example, “C1,C2” will group by column 1, then column 2. |
| Formulas | The aggregation functions used to calculate the grouped output, for example “SUM(V4)” where 4 refers to the column number, i.e. Sum the values in column 4 for each grouped set. |
| Calculate Error Value | The value that will be returned if the aggregation fails. |
Aggregation at multiple grouping levels
Aggregation can be performed at multiple grouping levels within one function, where grouping levels are denoted as G1, G2 etc. In other words, when grouping by three columns (“C1,C2,C3‘), G1 refers to ‘C1‘, G2 refers to ‘C1,C2‘ and G3 refers to “C1,C2,C3”.
For example: four columns in a range contain a list of Codes, Years, Countries and Prices, and these are grouped by the first three columns: ‘C1,C2,C3‘.
To sum the Price per Code:
=GroupBy(Range, ‘C1,C2,C3‘, ‘SUM(G1V4)‘) (G1 = grouping level 1, V4 = column 4)
To sum the Price per Code and Year:
=GroupBy(Range, ‘C1,C2,C3‘, ‘SUM(G2V4)‘)
To sum the Price per Code, Year and Country:
=GroupBy(Range, ‘C1,C2,C3‘, ‘SUM(G3V4)‘)
Note: in the latter example, the same result could be achieved by =GroupBy(Range, ‘C1,C2,C3‘, ‘SUM(V4)‘) as G3 is the lowest grouping level, and the lowest grouping level is used by default.
Note: to perform all grouping levels within one function, the examples above can be combined:
=GroupBy(Range, ‘C1,C2,C3‘, ‘SUM(G1V4), SUM(G2V4), SUM(G3V4)‘)
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Use GroupBy to calculate Sum, Average and Largest for each group
Example 2. Using GroupBy to Count items in each group
Example 3. Group and Sum at multiple levels – by Sector and by Asset within a Sector
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Use GroupBy to calculate Sum, Average and Largest for each group
The GroupBy() function is used to output the total return, the average return and the highest return, per fund and year.
| Range | Total, average and highest purchases per fund and year | |||
| Fund | Asset | Year | % Return | |
| AGRI PROVIDENT FUND | MBD | 2013 | 10 | =GroupBy(A3:D14,”C1,C3″,”SUM(V4),AVERAGE(V4),LARGE(V4,1)”) |
| AGRI PROVIDENT FUND | KVJ | 2013 | 20 | |
| AGRI PROVIDENT FUND | GMP | 2013 | 17 | |
| NNN LIFE LIMITED | YKG | 2013 | 45 | |
| NNN LIFE LIMITED | PHO | 2013 | 12 | |
| NNN LIFE LIMITED | BSF | 2013 | 14 | |
| AGRI PROVIDENT FUND | MBD | 2014 | 12 | |
| AGRI PROVIDENT FUND | KVJ | 2014 | 15 | |
| AGRI PROVIDENT FUND | GMP | 2014 | 25 | |
| NNN LIFE LIMITED | YKG | 2014 | 32 | |
| NNN LIFE LIMITED | PHO | 2014 | 13 | |
| NNN LIFE LIMITED | BSF | 2014 | 11 | |
| Range | Output | |||||||
| Fund | Asset | Year | % Return | Total | Average | Highest | ||
| AGRI PROVIDENT FUND | MBD | 2013 | 10 | AGRI PROVIDENT FUND | 2013 | 47 | 16 | 20 |
| AGRI PROVIDENT FUND | KVJ | 2013 | 20 | AGRI PROVIDENT FUND | 2014 | 52 | 17 | 25 |
| AGRI PROVIDENT FUND | GMP | 2013 | 17 | NNN LIFE LIMITED | 2013 | 71 | 24 | 45 |
| NNN LIFE LIMITED | YKG | 2013 | 45 | NNN LIFE LIMITED | 2014 | 56 | 19 | 32 |
| NNN LIFE LIMITED | PHO | 2013 | 12 | |||||
| NNN LIFE LIMITED | BSF | 2013 | 14 | |||||
| AGRI PROVIDENT FUND | MBD | 2014 | 12 | |||||
| AGRI PROVIDENT FUND | KVJ | 2014 | 15 | |||||
| AGRI PROVIDENT FUND | GMP | 2014 | 25 | |||||
| NNN LIFE LIMITED | YKG | 2014 | 32 | |||||
| NNN LIFE LIMITED | PHO | 2014 | 13 | |||||
| NNN LIFE LIMITED | BSF | 2014 | 11 | |||||
Example 2. Using GroupBy to Count items in each group
The GroupBy() function is used to output the number of assets in each fund’s portfolio. Note: In this example ‘COUNTA’ is used, as ‘COUNT’ only counts numeric values.
| Range | No. of assets | |
| Fund | Assets | |
| TNT MEDICAL SCHEME | GMP | =GroupBy(A3:B11,”C1″,”COUNTA(V2)”) |
| TNT MEDICAL SCHEME | YKG | |
| TNT MEDICAL SCHEME | PHO | |
| TNT MEDICAL SCHEME | BSF | |
| NNN LIFE LIMITED | QGW | |
| NNN LIFE LIMITED | QJU | |
| TOP SOLUTIONS EQUITY FUND | THT | |
| TOP SOLUTIONS EQUITY FUND | GTE | |
| TOP SOLUTIONS EQUITY FUND | JWQ | |
| Range | Output | ||
| Fund | Assets | Fund | Assets |
| TNT MEDICAL SCHEME | GMP | NNN LIFE LIMITED | 2 |
| TNT MEDICAL SCHEME | YKG | TNT MEDICAL SCHEME | 4 |
| TNT MEDICAL SCHEME | PHO | TOP SOLUTIONS EQUITY FUND | 3 |
| TNT MEDICAL SCHEME | BSF | ||
| NNN LIFE LIMITED | QGW | ||
| NNN LIFE LIMITED | QJU | ||
| TOP SOLUTIONS EQUITY FUND | THT | ||
| TOP SOLUTIONS EQUITY FUND | GTE | ||
| TOP SOLUTIONS EQUITY FUND | JWQ | ||
Example 3. Group and Sum at multiple levels – by Sector and by Asset within a Sector
The GroupBy() function is used to calculate the total value of each sector and asset (grouping level 2), and the total value of each sector (grouping level 1).
| Range | Total per sector and asset, Total per sector | |||
| Sector | Fund No | Asset | Value | |
| Basic Materials | 5000 | KVJ | 5250 | =GroupBy(A3:D12,”C1,C3″,”SUM(G2V4),SUM(G1V4)”) |
| Consumer Goods | 5010 | XDU | 9822 | |
| Basic Materials | 5020 | CEB | 1167 | |
| Consumer Goods | 5030 | XDU | 10311 | |
| Consumer Goods | 5040 | XDU | 410 | |
| Industrials | 5050 | ZQE | 1350 | |
| Consumer Goods | 5060 | NHP | 59 | |
| Consumer Goods | 5070 | QGW | 180 | |
| Consumer Goods | 5080 | QJU | 40 | |
| Industrials | 5090 | UAG | 16 | |
| Range | Output | ||||||
| Sector | Fund No | Asset | Value | Sector | Asset | Total per sector and asset | Total per sector |
| Basic Materials | 5000 | KVJ | 5250 | Basic Materials | CEB | 1167 | 6417 |
| Consumer Goods | 5010 | XDU | 9822 | Basic Materials | KVJ | 5250 | 6417 |
| Basic Materials | 5020 | CEB | 1167 | Consumer Goods | NHP | 59 | 20822 |
| Consumer Goods | 5030 | XDU | 10311 | Consumer Goods | QGW | 180 | 20822 |
| Consumer Goods | 5040 | XDU | 410 | Consumer Goods | QJU | 40 | 20822 |
| Industrials | 5050 | ZQE | 1350 | Consumer Goods | XDU | 20543 | 20822 |
| Consumer Goods | 5060 | NHP | 59 | Industrials | UAG | 16 | 1366 |
| Consumer Goods | 5070 | QGW | 180 | Industrials | ZQE | 1350 | 1366 |
| Consumer Goods | 5080 | QJU | 40 | ||||
| Industrials | 5090 | UAG | 16 | ||||