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.

Average Attendace formula in Exce

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.

Average Attendance Formula in Excel by arithmetic function

Press ENTER.

To calculate the percentage of attendance, enter this formula in cell J5:

=I5/$F$14

Press ENTER.

Average Attendance Formula in Excel with percentage

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

Select the ranges in the percentage column from J5 to J12.
Click the Percentage sign in the number tab.

average attendence formula

This will be the result.

Method 2 – Average Attendance Using the Average Function

Click on cell I5 and enter the following formula:

=AVERAGE(C5:H5)

Average Attendance Formula in Excel

This averages all values from C5 to H5.

Calculate the percentage of attendance in cell J5 using Method 1.

=I5/$F$14

average attendance with percentage

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

average attendence formula with percentage format

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

The data sheet is ready.

Method 3 – Average Attendance Using the Formula Ribbon

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

average attendence with autosum

Press ENTER.

Use AutoFill to complete the series.

average attendence using autosum

Method 4 – Average Attendance Using a Shortcut Key

If you’re comfortable with keyboard shortcuts, press ALT + M.

Average Attendance Formula in Excel using shortcut

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

Average Attendance Formula in Excel by shortcut keys

Press ENTER.

Average Attendance Formula in Excel methods

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

average attendence using shortcut key

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)

average attendence using sumproduct
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)).