Excel Functions: Working with Numbers

To draw insight from a very large dataset of numbers, it is frequently useful to summarize the numbers into a single value, such as the count number, the total of the values, or the mean of the values. This single value is sometimes referred to as a summary statistic. The Excel functions used to find them are called aggregate functions. This blog introduces a list of common aggregate functions.

count function

The count function counts the number of numerical values specified as the arguments and has the following syntax: 

= count(value1, [value2],…)

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula =count(A1:A7) returns the result of 5 since there are 5 numerical values (cells A2, A3, A4, A5, and A7). The text in cell A1 and the error in cell A6 are not numerical values and are thus ignored.

Example 2:
The formula = count(B1:B7) returns the result of 4. Datetime data (cells B2, B3, B5, and B6) are treated as numerical values. The text in cell B1 and the empty cells B4 and B7 are ignored.

sum function

The sum function finds the total of the numerical values specified as the arguments and has the following syntax: 

= sum(number1, [number2],…)

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula = sum (75,49,23) adds up the 3 arguments and returns the result of 147.

Example 2:
The formula = sum (A1, A3, B2, B4) adds up the values in the 4 cells and returns the result of 196.

Example 3:
The formula = sum (A1:A4, B1:B2) adds up the values in the range A1 through A4 and B1 through B2. The function ignores the text in cell B1 and returns the result of 194.

average function

The average function finds the mean value of the numerical arguments specified. It performs the same task as dividing the sum by the count and has the following syntax:

=average(number 1, [number2],…)

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula = average (75, 49, 23, 12) adds up the 4 arguments and divides the sum by the count number of 4. This returns the result of 39.75.

Example 2:
The formula = average (A1:B4) adds up the 6 numerical arguments and divides the sum by the count number 6 (the text in cell B1 and the empty cell B3 are not counted). This returns the result of 42.83.

max and min functions

The max and min functions find the largest and smallest numerical arguments specified and have the following syntaxes:

• =max(number 1, [number2],…)
• =min(number 1, [number2],…)

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula = max (A1:B4) returns the greatest number of 75.
The formula = min (A1:B4) returns the smallest number of 12.

Example 2:
The formula = max(min(A1:A4), B1:B4) first finds the smallest number of 12 in the range A1 through A4. It then finds the largest number among 12, 35, and 63, thereby returning the result of 63.

large and small functions

The large and small functions find the nth largest and smallest numerical value in a dataset. They have the following syntaxes:

• =large(array,nth)
• =min(array,nth)

Assume the following dataset [you may paste it into Excel]:

Example:
The formula = large(A1:B4, 2) returns 63, the second largest numerical value within the range A1 through B4.
The formula = small(A1:A4, 3) returns 35, the third smallest numerical value within the range A1 through A4.

sumproduct function

The sumproduct function multiplies different datasets with the same length and returns the sum of the products. It has the following syntax:

=sumproduct(array1, [array2],[array3],…)

Assume the following dataset [you may paste it into Excel]:

Example:
The formula = sumproduct(B2:B6, C2:C6) returns $61, the sum of the sale from all the products (unit price × quantity sold).

abs function

The abs function returns the absolute value of a numerical number. It has the following syntax:

=abs(number)

Assume the following dataset:

Example:
The formulas = abs(A2) and = abs(A4) return the absolute values of 52.83 and 146.55, respectively.

At Gleim, we know learning data analytics is vital for future accounting professionals. That is why we are offering these series of data analytics blogs and continually updating all of our CPA, CMA, and CIA Review materials with the necessary information you need to pass your exams.

We’ll continue our weekly blog series. Check back regularly for exam news and study tips!

Excel Lessons
Excel Basics
Excel Shortcuts
Excel Calculation Rules
Cell References
Excel Functions
Function Basics
Working with Numbers
Working with Datetime Data
Working with Text Data
Logical Testing
Summarizing Data
Filtering and Sorting
Lookup Functions
Tables
Pivot Tables