Mathematical Function in Excel

7 Mathematical Functions used in MS Excel with Examples

  1. SUM
  2. AVERAGE
  3. AVERAGEIF
  4. COUNTA
  5. COUNTIF
  6. MOD
  7. ROUND
Mathimatical-Function-in-Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Mathematical Function in Excel (wallstreetmojo.com)

Let us discuss each of them in detail –

You can download this Mathematical Function Excel Template here – Mathematical Function Excel Template

#1 SUM

If you want to SUM values of a number of cells quickly, we can use the SUM in excelSUM In ExcelThe SUM function in excel adds the numerical values in a range of cells. It is categorized under the math and trigonometry function entered by typing “=SUM” followed by the values to be summed. The values can be numbers, cell references or ranges.read more of the mathematics category.

For example, look at the below data in excel.

Math Function - Using Sum Function 1

From this, we need to find what the total production qty and total salary is.

Math Function - Using Sum Function 1-1

Open SUM function in the G2 cell.

Math Function - Using Sum Function 1-2

A select the range of cells from C2 to C11.

Math Function - Using Sum Function 1-3

Close the bracket and hit the enter key to get the total production qty.

Math Function - Using Sum Function 1-4

So, the total production quantity is 1506. Similarly, 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. Out of these overall total of employees, we need to find the average salary per employee.

Using Average function 1

Open the AVERAGE functionAVERAGE FunctionThe AVERAGE function in Excel gives the arithmetic mean of the supplied set of numeric values. This formula is categorized as a Statistical Function. The average formula is =AVERAGE(read more 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; for further drill-down, we want to know what is the average salary based on gender. What is the average salary of Male & Female?

Using Averageif function 1
  • The first parameter of this function is Range; choose cells from B2 to B11.
Using Averageif function 1-1
  • In this range, we need to take into consideration only male employees, so enter the criteria as “M.”
Using Averageif function 1-2
  • Next, we need to choose what the average range is, i.e., from D1 to D11.
Using Averageif function 1-3
  • So, the average salary of male employees is $4,940; similarly, apply the formula to find the female average salary.
Using Averageif function 1-4

The female average salary is $4,880.

#4 COUNTA

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

Using Counta function 1

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 are there.

Excel Mathematical Function (Using Countif) 1
  • The range is nothing 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
  • Criteria will in the selected Range what do we need to count??? Since we need to count how many male employees are there, give the criteria as “M.”
Excel Mathematical Function (Using Countif) 1-2
  • Similar copy the formula and change the criteria from “M” to “F.”
Excel Mathematical Function (Using Countif) 1-3

#6 MOD

MOD function will return the remainder when one number is divided by another number. For example, when you divide number 11 by 2, we 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 number 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 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 you can see above, 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.

Things to Remember

  • 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 the changes happen in referenced cells will impact formula cells instantly.
  • COUNTA function will count all the non-empty cells, but COUNT function in excel counts only numerical cell values.

This has been a guide to Mathematical Function in Excel. Here we discuss how to calculate mathematical function in excel using Sum, Average, Averageif, Counta, Countif, Mod, and Round formulas along with practical examples. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>