## 7 Mathematical Functions used in MS Excel with Examples

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

Let us discuss each of them in detail –

### #1 SUM

If you want to SUM values of a number of cells quickly we can use the SUM function of the mathematics category.

For example, look at the below data in excel.

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

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

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

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

So, the total production quantity is **1506**. Similarly, apply the same logic to get the total salary amount.

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

Open the AVERAGE function 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, for further drill-down we want to know what is the average salary based on gender. What is the average salary of Male & Female?

- This can be achieved by using the
**AVERAGEIF**function.

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

- In this range, we need to take into consideration only male employees so enter the criteria as
**“M”.**

- Next, we need to choose what the average range is i.e. from
**D1 to D11**.

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

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

### #4 COUNTA

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

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

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.

- So this can be done by using the “COUNTIF” function. COUNTIF counts the cells based on the criteria given.

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

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

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

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

- By applying 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 number 3.25 to 3 and 3.75 to 4.

- This can be done by using a
**ROUND**function in excel.

- Open ROUND function in
**C2**cells.

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

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

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 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 counts only numerical cell values.

### Recommended Articles

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 –

- Use Excel to Calculate ROI
- Compare Two Excel Dates
- Carriage Return in Excel Cell
- Divide Excel Cell
- EVEN Excel Function
- How to Forecast in Excel?
- Top Excel Hacks
- Percentage Change in Excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion