## 7 Mathematical Functions used in MS Excel with Examples

- SUM
- AVERAGE
- AVERAGEIF
- COUNTA
- COUNTIF
- MOD
- ROUND

##### Table of contents

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.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

### #1 SUM

If we want to SUM values of several cells quickly, we can use the SUM in excelSUM In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more for the mathematics category.

For example, look at the below data in Excel.

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

Open the SUM function in the **G2** cell.

And select the range of cells from **C2 to C11**.

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

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

### #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.

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.

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

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?

- We can achieve this by using the
**AVERAGEIF**function.

- The first parameter of this function is
**range.**For example, choose cells from**B2 to B11**.

- We need to consider only male employees in this range, so enter the criteria as
**“M.”**

- Next, we need to choose the average range,
**D1 to D11**.

- So, the average salary of male employees is
**$4,940.**Similarly, we must apply the formula to find the average female salary.

The female average salary is **$4,880**.

### #4 COUNTA

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

- To find the number of employees, we need to use the
**COUNTA**function in excel.

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.

- So we can do this by using the “COUNTIF” function. The COUNTIF counts the cells based on the criteria given.

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

- 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**.”

- Similarly, copy the formula and change the criteria from “
**M**” to “**F**.”

### #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.

- By applying a simple MOD function, we can find the remainder value.

### #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.

- We can do this by using a
**ROUND**function in excel.

- Open ROUND functionROUND FunctionROUND is a built-in Excel function that calculates the round number of a given number using the number of digits as an argument. The number to be rounded up to and the number of digits to be rounded up to are the two arguments to this formula.read more in
**C2**cells.

- Select the
**Number**as a B2 cell.

- Since we are rounding the value to the nearest integer
**number of digits**will be 0.

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

### Recommended Articles

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