Functions for data aggregation
Aggregation is a term that signifies unification and calculation. It combines one or more data sets, which are given as function arguments, for performing a mathematical operation. This may be: summing up a series of data, counting, averaging, calculating the minimum or maximum value of a given set of numbers… Aggregate functions are very commonly used and should know them well.
One of the most widely used Excel functions is the one used to sums data. It is called the SUM function. Here’s how its syntax looks:
SUM (<argument1>, <argument2>, … , <argumentn>)
The simplest way to apply this function is to select the range of cells we want to summon. For example, if we want to summarize cells B2, B3 and B4 we write a formula in which, as an argument of function, we enter the following range:
=SUM(B2:B4)
Don’t forget, when we enter a formula should always start with “=” sign. If we want further to summarize cells C3 and C4 should write following formula:
=SUM(B2:B4,C3:C4)
Although in this case the function has only two arguments, it is two cell circumferences. As an argument we could, for example, specify the full column B and in that case the function would look like this:
=SUM (B:B,C3:C4)
And we could have summed these cells by using the address of each of them as a special argument:
=SUM(B3,B4,B5,C3,C4)
Whatever way to choose, we always separate the arguments by comma (,) or semicolon (;). The symbol used to separate the function arguments depends on the regional settings. With traditional adjustments it is usually a comma, and in Serbian adjustments it is a semicolon. When you enter arguments, you will notice that Excel automatically feeds the arguments and marked the ranges in the same colors. This is another facilitator that aims to provide us with an insight into what information is being aggregated.
These two functions are used to calculate maximum (MAX) or minimum (MIN) of a given data range, and theri syntaxes are:
MAX (<argument1>, <argument2>, … , <argumentn>)
MIN (<argument1>, <argument2>, … , <argumentn>)
One more, commonly used, function is a one we use for counting (COUNT). Its syntax is:
COUNT (<argument1>, <argument2>, … , <argumentn>)
The COUNT function is used to count the cells that contains numbers. There are a few more counting functions. For example, the COUNTA function counts all the cells in the specified range that contain a value (number or text), and the COUNTBLANK function counts only the “empty” cells. When using the last two functions, attention should be paid to the fact that the cell that contains the space symbol also contains the value, even though it does not.
If there is a need to calculate product of a range of cells we’ll use PRODUCT function. Its syntax is:
PRODUCT (<argument1>, <argument2>, … , <argumentn>)