A common task in business and in everyday life is calculating the average salary. Excel makes calculating the average salary easy. Excel has a variety of formulas for calculating averages. The formulas we learn to calculate average salaries will be useful in our practical work. In this article, You learn 6 effective methods to calculate the average salary in Excel.

**Watch Video – Calculate Average Salary in Excel**

## Calculate Average Salary in Excel: 6 Methods

Let’s first introduce our dataset. There are **10** employees and their respective monthly salaries. Our goal is to calculate the average salaries of the **10** employees.

### 1. Use AVERAGE Function to Calculate Average Salary

For calculating the average, the **AVERAGE function** is the simplest. Whenever there are no criteria, we can use them to find the direct average. Follow the steps below to apply this function.

📌 **Steps:**

- Copy the following formula into cell
**C15,**then press**ENTER.**

`=AVERAGE(C5:C14)`

Here, **C5:C14** is the salary of the employee from cells **B5** to **B14.**

### 2. Use AVERAGEA Function to Calculate Average Salary If Any Input Is Unavailable

If our selected argument cells contain text, we can use the **AVERAGEA function** to calculate averages. Based on this definition, **AVERAGEA** evaluates text as zero, logical values **TRUE** as 1, and logical values **FALSE** as zero. To demonstrate how **AVERAGEA** works, we have changed Henry’s salary to Not Available.

📌 **Steps:**

- Write down the following formula into cell
**C15,**and then press**ENTER**key.

`=AVERAGEA(C5:C14)`

### 3. Utilizing AVERAGEIF Function to Calculate Conditional Average Salary

The **AVERAGEIF function** calculates the arithmetic mean of cells that satisfy particular criteria within the range provided. To demonstrate how the **AVERAGEIF** function works, we keep the name Luke four times in the dataset. Then, our goal is to calculate only Luke’s average salary by using the **AVERAGEIF** function.

📌 **Steps:**

- Put the following formula into cell
**C15,**and then press**ENTER.**

`=AVERAGEIF(B5:B14,"Luke",C5:C14)`

Here, **B5:B14** refers to the Name of the employee from cells **B5** to **B14.** Luke is the criteria here, and this function will calculate the average of Luke’s salary only.

### 4. Applying SUM and COUNTA Functions

Another easy way to calculate the average salary in Excel is to apply the **SUM** and **COUNTA functions** jointly.

📌 **Steps:**

- Copy the following formula into cell
**C15,**then press**ENTER.**

`=SUM(C5:C14)/COUNTA(C5:C14)`

Here, the **SUM function** first calculates the summation from cells **C5** to **C14,** which we have written in the formula. The **COUNTA** function counts the argument numbers from cell **C5** to **C14.** Then the result from the **SUM** function is divided by the result of the **COUNTA** function to compute the average.

### 5. Using SUBTOTAL Function

**SUBTOTAL** is one of Excel’s most powerful functions. There are many ways in which we can use this function.

📌 **Steps:**

- Write down the following formula into cell
**C15,**and then press**ENTER.**

`=SUBTOTAL(1,C5:C14)`

After taking **SUBTOTAL** in the formula, we can see different function names in the list. Among them, **AVERAGE** is the first one. Here, 1 refers to this **AVERAGE** function.

### 6. Applying AVERAGE and LARGE or SMALL Functions

#### 6.1 Finding Average of 3 Largest Salaries

We need to combine **AVERAGE** and **LARGE functions **when calculating the average of certain limited large values.

📌 **Steps:**

- Keep the following formula into cell
**C15,**then press**ENTER**key.

**=AVERAGE(LARGE(C5:C14,{1,2,3}))**

Here, the formula **LARGE(C5:C14,{1,2,3}))** will first return the largest 3 values from cell **C5** to **C14.** Then the **AVERAGE** function computes the average of the results of the first formula.

#### 6.2 Finding Average of 3 Smallest Salaries

In the same way that the **LARGE** function works, the **SMALL fun****c****tion** does as well.

📌 **Steps:**

- Copy the following formula and paste it into cell
**C15,**then press**ENTER.**

`=AVERAGE(SMALL(C5:C14,{1,2,3}))`

Here, the formula **SMALL(C5:C14,{1,2,3}))** will first return the smallest 3 values from cell **C5** to **C14.** Then the **AVERAGE** function computes the average of the results of the first formula.

You can download the following practice workbook that we have used to prepare this article

## Conclusion

In this tutorial, I have discussed 6 effective methods to calculate average salary in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

**<< Go Back to Salary | Formula List | Learn Excel**