# How to Use the Average Attendance Formula in Excel (5 Methods)

## Dataset Overview

For your better understanding, we will use a sample dataset. The data set contains, Names, Months, Number of attendances, and number of total working days. We will calculate the average attendance per month, as well the average percentage per month.Â

### Method 1 – Average Attendance by Arithmetic Calculation

• In cell I5, enter the following formula:
`=(C5+D5+E5+F5+G5+H5)/(\$F\$15)`
• This calculates the sum of attendance for six months and divides it by the total number of months (which is 6). We use an absolute cell reference for F15 to ensure consistent division.

• Press ENTER.

• To calculate the percentage of attendance, enter this formula in cell J5:
`=I5/\$F\$14`

• Press ENTER.

• Use AutoFill to extend the formulas to the rest of the series.

• Select the ranges in the percentage column from J5 to J12.Â

• This will be the result.

Read More:Â How to Average Filtered Data in Excel

### Method 2 – Average Attendance Using the Average Function

• Click on cell I5 and enter the following formula:
`=AVERAGE(C5:H5)`

• This averages all values from C5 to H5.

• Calculate the percentage of attendance in cell J5 using Method 1.
`=I5/\$F\$14`

• Press ENTER.
• Use AutoFill to fill the rest of the series.

• Convert the percentage values in the Percent column by selecting the range and clicking the Percentage sign in the number tab.

Read More: How to Calculate Class Average in Excel

### Method 3 – Average Attendance Using Formula Ribbon

• Click on cell I5.
• Go to the Formulas ribbon and select AverageÂ from the AutoSum feature.

• Press ENTER.

• Use AutoFill to complete the series.

### Method 4 – Average Attendance Using a Shortcut Key

• If youâ€™re comfortable with keyboard shortcuts, press ALT + M.

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

• Press ENTER.

• Right-click and drag it down to AutoFill the series.

Related Content: How to Calculate Average Percentage of Marks in Excel

### Method 5 – Average Attendance Using SUMPRODUCT Function in Excel

• Click on cell D12.
• Enter the following formula:
`=SUMPRODUCT(C5:C10,D5:D10)/SUM(C5:C10)`

Hereâ€™s how it works:

• The SUMPRODUCT function calculates the sum of products for each month: (C5 * D5) + (C6 * D6) + â€¦ + (C10 * D10).
• We then divide this sum by the total number of employees (SUM(C5:C10)).
• Press ENTER to get the results.

## Practice Section

Feel free to practice using the provided sheet.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF