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.

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.

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

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

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

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

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.

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.

Similar Readings:

• Excel Formula to Find Displaced Moving Average (With Easy Steps)
• How to Determine Triple Exponential Moving Average in Excel
• How to Generate Moving Average in Excel Chart (4 Methods)
• How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
• [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)

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 (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,

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.

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.

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

• How to Calculate Average Percentage Change in Excel (3 Simple Ways)
• Moving Average Formula in Excel (8 Uses with Examples)
• How to Calculate Average and Standard Deviation in Excel
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
• How to Calculate Average of Text in Excel (2 Ways)