Contact Us : 800.874.5346        International: +1 352.375.0772
Contact Us : 800.874.5346        International: +1 352.375.0772

Excel Functions: Working with Numbers

Data Analytics Lesson 6: 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.

Counting the number of Entries

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]:

A B
1 Number Date
2 10 1/1/2020
3 11 2/1/2020
4 12
5 13 4/1/2020
6 #VALUE! 5/1/2020
7 16

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.

Finding the total

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]:

A B
1 75 A
2 49 35
3 23
4 12 63

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.

Finding the average

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]:

  A B
1 75 A
2 49 35
3 23  
4 12 63

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.

Finding the maximum or minimum

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]:

  A B
1 75 A
2 49 35
3 23  
4 12 63

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.

Finding the nth largest or smallest number

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]:

  A B
1 75 A
2 49 35
3 23  
4 12 63

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.

Finding the sum of products

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]:

A B C
1 Product Unit_Price Quantity_Sold
2 A $ 1.00 9
3 B $ 1.50 10
4 C $ 2.00 3
5 D $ 2.50 4
6 E $ 3.00 7

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

Finding the absolute value

abs function

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

=abs(number)

Assume the following dataset:

A
1 Numbers
2 52.83
3 67.98
4 -146.55
5 -91.73
6 -49.33
7 48.13

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

Learning Excel Data Analytics with Gleim

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!

Python Lessons
Python Basics
Conditional Statements
Loops
Functions and Modules
Numerical Python (NumPy)
Pandas Basics
Pandas Data Capture and Cleansing
Merging and Grouping
Manipulating Text and Datetime Data
Visualization
Web Scraping
Errors and Exceptions