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

`=AVERAGE(C5:C14)`

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

`=AVERAGEA(C5:C14)`

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

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

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

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

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

`=SUBTOTAL(1,C5:C14)`

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

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

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

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

**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 the practice workbook.

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