Hafiz Islam
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.
Download Practice Workbook
You can download the following practice workbook that we have used to prepare this article
6 Methods to Calculate Average Salary in Excel
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.
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.
Similar Readings
- How to Calculate DA on Basic Salary in Excel (3 Easy Ways)
- Calculate Basic Salary in Excel (3 Common Cases)
- How to Create Salary Slip Format with Formula in Excel Sheet
- Calculate Bonus on Salary in Excel (7 Suitable Methods)
- How to calculate salary increase percentage in Excel [Free Template]
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.
Read More: How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)
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.
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.
=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.
Conclusion
In this tutorial, I have discussed 6 effective methods to calculate average salary in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Calculate Net Salary in Excel (With Easy Steps)
- Calculate Gross Salary in Excel (3 Useful Methods)
- How to Calculate Income Tax on Salary with Example in Excel
- Daily Wages Sheet Format in Excel (with Quick Steps)
- Attendance Sheet with Salary in Excel Format (with Easy Steps)
- Per Day Salary Calculation Formula in Excel (2 Suitable Examples)
- How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)
