Md. Sourov Hossain Mithun
In every educational institution and office, it’s a common task to calculate absenteeism percentages for students or employees. It helps to determine who is more absent and who is less absent. If you are searching for ways how to do it then you have come to the right place. We hope, this tutorial will show you two easy and useful methods to calculate absenteeism percentage in Excel.
What Is Absenteeism Percentage?
The percentage of unintentional absence because of sickness or any other causes is called the absenteeism percentage, absence percentage, or absenteeism rate. It shows the ratio between absence days and working days. The basic absenteeism percentage formula is:
- Absenteeism Percentage = (Total Absence Days / Total Working Days)*100%
How to Calculate Absenteeism Percentage in Excel: 2 Ways
In this section, we’ll demonstrate the two easy methods with sharp steps and vivid illustrations.
1. Calculating Individual Person-Based Absenteeism Percentage
In our first method, we’ll show how to calculate the absenteeism percentage for every person. For that, we’ll use a dataset that contains some employees’ information on working days and absent days.
Steps:
- Insert the following absenteeism formula into Cell F5–
=E5/C5- Then just hit the Enter button to get the absenteeism percentage of the first employee.
- Later, use the Fill Handle tool to copy the formula for the other employees.
After a while, you will get all the absenteeism percentages for all the employees.
2. Calculating Overall Absenteeism Percentage in Excel
Now we’ll determine the overall monthly and yearly absenteeism percentage for all the employees of a company. It will help to set and maintain an acceptable absenteeism rate for the company.
For this method, we modified the dataset which is containing the working days and absent days of every month for the average number of employees.
Steps:
- In Cell F5, type the following formula to get the average absenteeism rate for January-
=E5/D5- Next, press the Enter button for the output.
- To get the other average absenteeism rates for the other months, drag down the Fill Handle icon till the Cell F16.
Here are all the average absenteeism rates for every month.
Now we’ll calculate the absenteeism rates for a year. For that, we’ll need the total working days and total absent days for the year using the SUM function.
- Write the following formula in Cell D17 to get the total working days for the year-
=SUM(D5:D16)- Then hit the Enter button to get the sum.
- Next, to get the total absent days for the year, insert the following formula in Cell E17 –
=SUM(E5:E16)- Then press the Enter button to get the sum.
- Finally, to get the average absenteeism percentage for the year, type the following formula in Cell F18–
=E17/D17- After that, hit the Enter button to finish.
Absenteeism Percentage Calculator in Excel
In our workbook, you will get an absenteeism Excel template that you can use as an absenteeism percentage calculator.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
Conclusion
That’s all for the article. I hope the procedures described above will be good enough to calculate the absenteeism percentage in Excel. Feel free to ask any question in the comment section and please give me feedback.
Related Articles
- Make an Excel Spreadsheet Automatically Calculate Percentage
- How to Add a Percentage to a Number in Excel
- Excel Formula to Add Percentage Markup
- Calculate Percentage in Excel VBA
- Calculate Percentage Using Absolute Cell Reference in Excel
- How to Find the Percentage of Two Numbers in Excel
- How to Calculate Percentage of Total in Excel
- How to Calculate Total Percentage from Multiple Percentages in Excel
- How to Use Excel Formula to Calculate Percentage of Grand Total
- How to Calculate Cumulative Percentage in Excel
- How to Calculate Grade Percentage in Excel
- How to Calculate Percentage of Month in Excel
- How to Calculate Accuracy Percentage in Excel
- How to Calculate Win-Loss Percentage in Excel
- How to Calculate SLA Percentage in Excel
- How to Calculate Error Percentage in Excel
- How to Calculate Mean Percentage Error in Excel
- How to Calculate Remaining Shelf Life Percentage in Excel
- How to Calculate Percentage of Completion in Excel
- How to Calculate Percentage of Budget Spent in Excel
- How to Calculate Utilization Percentage in Excel
- How to Calculate Percentage of Percentage in Excel
- How to Calculate Percentage for Multiple Rows in Excel
- How to Calculate Savings Percentage in Excel
- How to Calculate Percentage of Filled Cells in Excel
- How to Calculate Productivity Percentage in Excel
- How to Calculate Percentage Based on Conditional Formatting
- How to Calculate Percentage in Excel Based on Cell Color
- How to Calculate Percentage Complete Based on Dates in Excel
- How to Convert Number to Percentage in Excel
- Convert Number to Percentage Without Multiplying by 100 in Excel
- How to Divide a Value to Get a Percentage in Excel
- How to Calculate Percentage above Average in Excel
- How to Apply Percentage Formula in Excel for Marksheet
- How to Apply Percentage Formula for Multiple Cells in Excel
- IF Percentage Formula in Excel
- How to Calculate Contribution Percentage with Formula in Excel
- How to Use Food Cost Percentage Formula in Excel
- How to Show One Number as a Percentage of Another in Excel
- How to Do Sum of Percentages in Excel
- How to Add Percentage to Price with Excel Formula
- How to Add 10 Percent to a Number in Excel
- How to Add 15 Percent to a Price in Excel
- How to Add 20 Percent to a Price in Excel
- How to Subtract a Percentage in Excel
- How to Subtract a Percentage from a Price in Excel
- How to Subtract 10 Percent in Excel
- How to Put Negative Percentage Inside Brackets in Excel
- Percentage Showing as Thousand in Excel
- How to Calculate Reverse Percentage in Excel
- Why Are My Percentages Wrong in Excel?
- How to Convert Percentage to Number in Excel
- How to Convert Percentage to Whole Number in Excel
- How to Remove Percentage in Excel
- How to Calculate Variance Percentage in Excel
- How to Calculate Percentage Change with Negative Numbers in Excel
