How to Calculate the Average Salary in Excel – 6 Methods

The sample dataset showcases 10 employees and their monthly salaries. To calculate the average salaries:

Method 1 – Use the AVERAGE Function to Calculate the Average Salary

Steps:

• Enter the following formula in C15 and press ENTER.

C5:C14 is the salary in B5:B14.

Method 2 – Use the AVERAGEA Function to Calculate the Average Salary If Any Input Is Unavailable

Steps:

• Use the following formula in C15 and press ENTER.

Method 3 – Utilizing the AVERAGEIF Function to Calculate a Conditional Average Salary

The name Luke is used four times in the dataset. Calculate  Luke’s average salary only.

Steps:

• Use the following formula in C15 and press ENTER.

B5:B14 refers to the Name of the employee In B5:B14. Luke is the criteria, and the function calculates the average of Luke’s salary only.

Method 4 – Applying the SUM and COUNTA Functions

Steps:

• Use the following formula in C15 and press ENTER.

The SUM function calculates the summation of C5:C14. The COUNTA function counts the argument numbers in C5:C14. The result from the SUM function is divided by the result of the COUNTA function to compute the average.

Method 5 – Using the SUBTOTAL Function

Steps:

• Use the following formula in C15 and press ENTER.

Different function names are displayed in the list. Here, 1 refers to the AVERAGE function.

Method 6 – Applying the AVERAGE and the LARGE or SMALL Functions

6.1 Finding the Average of the 3 highest Salaries

Steps:

• Use the following formula in C15 and press ENTER.

LARGE(C5:C14,{1,2,3})) will return the 3 largest values in C5: C14. The AVERAGE function computes the average of the results of the first formula.

6.2 Finding the Average of the 3 Lowest Salaries

Steps:

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

SMALL(C5:C14,{1,2,3})) will return the 3 smallest values in C5:C14. The AVERAGE function computes the average of the results of the first formula.

Download Practice Workbook

Download the practice workbook.

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