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.
 Click the Percentage sign in the number tab.
 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.
The data sheet is ready.
Read More: How to Calculate Class Average in Excel
Method 3 – Average Attendance Using the 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.
 Rightclick 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.
Download Practice Workbook
You can download the practice workbook from here:
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!