How to Calculate Average Salary in Excel (6 Effective Methods)

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.

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.

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.

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.

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.

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.

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.

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 function does as well.

📌 Steps:

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

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.

Download Practice Workbook

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