Average Attendance Formula in Excel (5 Ways)

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 Attendace formula in Exce


Download Practice workbook


5 Quick Methods to Average Attendance Formula in Excel

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)

Average Attendance Formula in Excel by arithmetic function
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 want 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.

Average Attendance Formula in Excel with percentage

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.

average attendence formula

We will get the following result.


Simple and easy.

Read More: How to Calculate Average in Excel (Including All Criteria)


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  type the following in the formula.

=AVERAGE(C5:H5)

Average Attendance Formula in Excel

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

average attendance with percentage

Now press ENTER key. At this point, we will use AutoFill to fill the rest of the series.

average attendence formula with percentage format

Now, to convert the values in percentage at 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.

Related Content: How to Calculate Class Average in Excel (6 Easiest Methods)


Method 3: Average Attendance Using Formula Ribbon in Excel

We can use the average attendance formula with simple few clicks also. 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.

average attendence with autosum
Now, press the ENTER key.

After that, use AutoFill to fill the rest of the series.

average attendence using autosum

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.


Similar Readings:


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.

Average Attendance Formula in Excel using shortcut

Now, press U, then press A. Excel will automatically select the cells.

Average Attendance Formula in Excel by shortcut keys
Now, press the ENTER key.

Average Attendance Formula in Excel methods
Now, click the right button on the mouse and drag it down to AutoFill the series.

average attendence using shortcut key

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 (Top 4 Methods)


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 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)

average attendence using sumproduct
Here, the SUMPRODUCT function performs this calculation: (C5* D5) + (C6 * D6) +….(C10*D10). Then we are dividing it by the Sum of the Number of Employees.
Now, press the ENTER key and our result will look like the following image.

Average Attendance Formula in Excel using sumproduct

So, we got our result.


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.

average attendance methods in Excel


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. You may also browse this site’s other Excel-related topics.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo