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.


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)

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.

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.

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

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)

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)).
  • Press ENTER to get the results.

Average Attendance Formula in Excel using sumproduct


Practice Section

Feel free to practice using the provided sheet.

average attendance methods in Excel


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< 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!
Mahbubur Rahman
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo