Mathematical Function In Excel

Last Updated :

21 Aug, 2024

Blog Author :

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Are Mathematical Functions In Excel?

Mathematical functions in Excel is used to find values which require mathematical formulas. One of the reasons why users find Excel extremely helpful is because of the inbuilt functions and the possibility to create different combination of formulas.

In this article, let us learn the basic mathematical functions we can use in Excel.

Mathimatical-Function-in-Excel
  1. The seven common mathematical functions used in MS Excel are SUM, AVERAGE, AVERAGEIF, COUNTA, COUNTIF, MOD, and ROUND.
  2. All mathematical functions in Excel are categorized under "Math & Trigonometry." Once a cell reference is given, the formula will be dynamic, and any changes in referenced cells will instantly impact formula cells.
  3. To get an accurate count of cells in Excel, it's crucial to use the proper function. The COUNT function counts only numeric cell values, whereas the COUNTA function counts all non-empty cells, including those that contain text, dates, or logical values.

7 Mathematical Functions Used In MS Excel With Examples

  1. SUM
  2. AVERAGE
  3. AVERAGEIF
  4. COUNTA
  5. COUNTIF
  6. MOD
  7. ROUND

Let us discuss each of them in detail.

#1 - SUM

If we want to SUM values of several cells quickly, we can use the SUM in Excel for the mathematics category.

For example, look at the below data in Excel.

Math Function - Using Sum Function 1

We need to find the total production quantity and total salary from this.

Math Function - Using Sum Function 1-1

Open the SUM function in the G2 cell.

Math Function - Using Sum Function 1-2

And select the range of cells from C2 to C11.

Math Function - Using Sum Function 1-3

Close the bracket and press the "Enter" key to get the total production quantity.

Math Function - Using Sum Function 1-4

So, the total production quantity is 1,506. Similarly, we must apply the same logic to get the total salary amount.

Math Function - Using Sum Function 1-5

#2 - AVERAGE

Now, we know what overall sum values are. Next, we need to find the average salary per employee out of these overall employees.

Using Average function 1

Open the AVERAGE function in the G4 cell.

Using Average function 1-1

Select the range of cells for which we are finding the average value, so our range of cells will be from D2 to D11.

Using Average function 1-2

So the average salary per person is $4,910.

#3 - AVERAGEIF

We know the average salary per person; we want to know the average salary based on gender for further drill-down. What is the average salary of males and females?

Using Averageif function 1
  • The first parameter of this function is range. For example, choose cells from B2 to B11.
Using Averageif function 1-1
  • We need to consider only male employees in this range, so enter the criteria as "M."
Using Averageif function 1-2
  • Next, we need to choose the average range, D1 to D11.
Using Averageif function 1-3
  • So, the average salary of male employees is $4,940. Similarly, we must apply the formula to find the average female salary.
Using Averageif function 1-4

The female average salary is $4,880.

#4 - COUNTA

Let us find out how many employees are there in this range.

Using Counta function 1

The COUNTA function will count the number of non-empty cells in the selected range of cells. So totally, there are 10 employees on the list.

#5 - COUNTIF

After counting the total number of employees, we may need to count how many male and female employees there are.

Excel Mathematical Function (Using Countif) 1
  • The range is in which range of cells we need to count since we need to count the number of male or female employees who choose cells from B2 to B11.
Excel Mathematical Function (Using Countif) 1-1
  • The criteria will be in the selected range. What do we need to count? Since we need to count how many male employees there are, give the criteria as "M."
Excel Mathematical Function (Using Countif) 1-2
  • Similarly, copy the formula and change the criteria from "M" to "F."
Excel Mathematical Function (Using Countif) 1-3

#6 - MOD

The MOD function will return the remainder when one number is divided by another. For example, dividing the number 11 by 2 will get the remainder as 1 because only till 10 number 2 can divide.

  • For example, look at the below data.
Using Mod 1
  • By applying a simple MOD function, we can find the remainder value.
Using Mod 1-1

#7 - ROUND

When we have fraction or decimal values, we may need to round those decimal values to the nearest integer number. For example, we need to round the numbers 3.25 to 3 and 3.75 to 4.

Excel Mathematical Function (Using Round) 1
Excel Mathematical Function (Using Round) 1-1
  • Select the Number as a B2 cell.
Excel Mathematical Function (Using Round) 1-2
  • Since we are rounding the value to the nearest integer number of digits will be 0.
Excel Mathematical Function (Using Round) 1-3

As we can see above, the B2 cell value 115.89 is rounded to the nearest integer value of 116, and the B5 cell value of 123.34 is rounded to 123.

Like this, we can use various mathematical functions in Excel to do mathematical operations in Excel quickly and easily.

Important Things To Note

  • All the mathematical functions in Excel are categorized under the "Math & Trigonometry" function in Excel.
  • Once the cell reference is given, the formula will be dynamic, and whatever changes happen in referenced cells will impact formula cells instantly.
  • The COUNTA function will count all the non-empty cells, but the COUNT function in Excel calculates only numeric cell values.

Frequently Asked Questions

1. Where are mathematical functions used?

Mathematical functions are building blocks used in designing machines, predicting disasters, treating diseases, studying economies, and maintaining aircraft. Understanding them is essential for excelling in these fields.

2. What is the use of mathematical functions?

A mathematical function defines the value of a dependent variable based on one or more independent variables. It can be represented through tables, formulas, graphs, or computer algorithms. Functions are important for academic and business applications, such as engineering, finance, and statistics. The choice of representation should be based on problem requirements and available resources.

3. What are the parts of a mathematical function?

A function has three parts: inputs, outputs, and a rule that assigns each input to exactly one output.

This article is a guide to Mathematical Functions in Excel. We discuss calculating mathematical functions in Excel using SUM, AVERAGE, AVERAGEIF, COUNTA, COUNTIF, MOD, and ROUND formulas and practical examples. You may learn more about Excel from the following articles: -