The AVERAGE function is part of the Excel Statistical functions category. The AVERAGE function in Excel computes the average (arithmetic mean) of two or more integers. This article will show you, step by step, the average attendance formula in Excel and how to use it.
For your better understanding, we will use a sample dataset. The data set contains, Names, Months, number of attendance, and number of total working days. We will calculate the average attendance per month. Also, we will calculate the average percentage per month if necessary.
Average Attendance Formula in Excel: 5 Quick Methods
There are several ways to calculate the average in Excel. We will see 5 simple and easy average attendance formulas in Excel.
Method 1: Average Attendance by Arithmetic Calculation
Average is used to give the Arithmetic Mean of a given series. We can calculate the average by our known formula sum of the values divided by the number of values.
First, type the following formula in cell I5.
=(C5+D5+E5+F5+G5+H5)/($F$15)
Now, press the ENTER key.
Here, Excel has calculated the sum of attendance for 6 months and divided it by the total number of months, which is 6.
We used Absolute Cell Reference in cell F15, as we wanted to divide the sum of days by a constant number of months.
Now, we will calculate the percentage of attendance. So, type the following formula in cell J5
=I5/$F$14
Here, by this formula, we are trying to calculate the percentage of attendance. We are dividing the number of attendance by the Number of Days Per Month.
Now, press the ENTER key.
At this point, we will use AutoFill to fill the rest of the series.
Now, select the ranges in the percent column from J5 to J12 and do as the following images show. Click the Percentage sign in the number tab.
We will get the following result.
Simple and easy.
Read More:Â How to Average Filtered Data in Excel
Method 2: Average Attendance by Average Formula in Excel
There is an in-built function Called AVERAGE in Excel. Let’s see how it works,
First, click on cell I5 and type the following in the formula.
=AVERAGE(C5:H5)
Here, we are telling Excel to average all the values from cell C5 to H5.
Now, press the ENTER key.
Now, we will calculate the percentage of attendance. So, type the following formula in cell J5
=I5/$F$14
Now press the ENTER key. At this point, we will use AutoFill to fill the rest of the series.
Now, to convert the values in percentage in the Percent column, select the range and click the Percentage sign in the number tab, look at the following image.
Finally, our data sheet is ready.
Read More: How to Calculate Class Average in Excel
Method 3: Average Attendance Using Formula Ribbon in Excel
We can use the average attendance formula with a simple few clicks. Let me show you how.
First, click on cell I5, then go to the Formulas ribbon and select Average from the AutoSum feature. As shown in the image.
Now, press the ENTER key.
After that, use AutoFill to fill the rest of the series.
Our result is ready. If we want to count the percentage then follow method 1. Here, Excel has calculated the sum of attendance for 6 months and divided it by the total number of months, which is 6.
Method 4: Average Attendance Using Shortcut Key in Excel
If you are comfortable with the keyboard then there is a keyboard shortcut available for the average attendance formula in Excel.
First, press ALT + M, and our worksheet will look like the following image.
Now, press U, then press A. Excel will automatically select the cells.
Now, press the ENTER key.
Now, click the right button on the mouse and drag it down to AutoFill the series.
Here you go, the average attendance is calculated for the workbook. Now, if you want further calculations like percentages, follow the procedure shown in method 1.
Related Content: How to Calculate Average Percentage of Marks in Excel
Method 5: Average Attendance Using SUMPRODUCT Function in Excel
This time we want to calculate the average attendance per employee using the SUMPRODUCT function and the SUM function. In the dataset, we can see that in the dataset the Number of Employees and their attendance each month is given. Now, we will see the average attendance per month for an employee.
First, click on cell D12 and type the following formula,
=SUMPRODUCT(C5:C10,D5:D10)/SUM(C5:C10)
Here, the SUMPRODUCT function performs this calculation: (C5* D5) + (C6 * D6) +….(C10*D10). Then we divide it by the Sum of the Number of Employees.
Now, press the ENTER key and our result will look like the following image.
So, we got our results.
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.
Download Practice Workbook
Conclusion
There are five different ways to average attendance in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
Related Articles
- How to Calculate Average, Minimum And Maximum in Excel
- How to Calculate Average of Averages in Excel
- How to Calculate Average True Range in Excel
- How to Calculate Average Percentage in Excel
- How to Calculate Average Revenue in Excel
- How to Calculate Average Quarterly Revenue in Excel
- How to Calculate Average Share Price in Excel
- How to Calculate Average Length of Stay in Excel
- How to Calculate Average Price in Excel
<< Go Back to Excel Average Formula Examples | How to Calculate Average in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!