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

If the selected cells contain text, use the AVERAGEA function. It evaluates text as zero, logical values TRUE as 1, and logical values FALSE as zero. Henry’s salary was changed to Not Available.

Steps:

• Use the following formula in C15 and press ENTER.

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

The AVERAGEIF function calculates the arithmetic mean of cells that meet criteria within the range provided.

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