Excel formula: Percent of students absent

Generic formula

=(total-attended)/total

Summary

To calculate the percentage of students absent in a given class, you can use simple formula that divides students absent (calculated by subtracting attending from total) by the total. In the example shown, the formula in E5, copied down, is:

=(C5-D5)/C5

The result is a decimal value that is formatted using the percentage number format.

Explanation

In this example, the goal is to answer the question “What percentage of students were absent from each class”. In other words, given a class with 30 students total, 27 of which were present, we want to return 10% absent. The general formula for this calculation, where “x” is the percent absent is:

x=absent/total

However, since we don’t have a column for the number of students absent in the table, we need to calculate this number as part of the formula:

x=(total-attended)/total
x=(30-27)/30
x=3/30
x=0.10

After we convert this to an Excel formula with cell references, the formula in E5 becomes:

=(C5-D5)/C5
=(30-27)/30
=3/30
=0.10

As the formula is copied down, the formula returns calculated “percent absent” for each class listed in the table. These results are decimal numbers formatted with the Percentage number format.

Formatting percentages in Excel

In mathematics, a percentage is a number expressed as a fraction of 100. For example, 55% is read as “Fifty-five percent” and is equivalent to 55/100 or 0.55. To display values like this with with a percent sign (%), apply Percentage number format.

Author

Dave Bruns

Related formulas

Get percentage of total

In this example, the goal is to work out the “percent of total” for each expense shown in the worksheet. In other words, given that we know the total is $1945, and we know Rent is $700, we want to determine that Rent is 36% of the total. The total…

Percent of goal

In this example, the objective is to calculate a percentage for each goal shown in column C of the table using the actual values in column D. In other words, given a goal of 100000, and an actual amount of 112000, we want to return 112% as the…

Get amount with percentage

In this example, the goal is to convert the percentages shown in column C to amounts, where the total of all amounts is given as $1945. In other words, if we know Rent is 36.0%, and the total of all expenses is $1945, we want to calculate that Rent…

Get percent of year complete

The goal in this example is to return the amount of time completed in a year as a percentage value, based on any given date. In other words, when given the date July 1, 2021, the formula should return 50% since we are halfway* through the year. *By…

Determining your Monthly Attendance Percentage

Posted by Nancy Paulson

The Monthly Attendance Percenatge is the average of the weekly attendance for your club.

Read a section of the RI Secretary’s Manual to understand who to include in your attendance reports.

Basic Process

On a weekly basis determine the attendance percentage. (Number of Members Present or Made Up) divided by (Number of Members Used in Calculating Attendance) multiplied by 100 equals the weekly attendance percentage.

At the end of the month, average the weekly percentages to get the monthly percentage. (Sum of all the weekly percentages) divided by (Number of meetings this month) equals the monthly attendance percentage.

Methods

Use a calculator following the formulas provided above.
Use the Excel spreadsheet provided in the download files to the left. Note you must have Excel or Open Office on your computer to use this file

How to Create Attendance Tracker in Exce

Why buy an expensive attendance management tool for your startup if you can track the attendance of the team in Excel? Yes! You can create an Attendance tracker in Excel easily. In this article, we will learn how to do so.

Step1: Create 12 sheets for Every Month in a workbook

If you plan to track attendance for a year, you will need to create each month’s sheet in Excel.

Step 2: Add Columns for each date in each month’s sheet.

Now create a table that contains the names of your teammates, a column for totals and 30 (or number of days a month) columns with date and weekday as column headings.

To get the name of weekday you can look up the calendar or you can use the formula to copy it in the rest of the cells.

=TEXT(date,”ddd”)

You can read about it here.

Format the weekends and holidays dark and fill them with fixed values like Weekend/Holiday as shown in the image below.

Do the same for each sheet.

Step 3. Fix the possible inputs using data validation for each open cell.

To allow users to only write P or A for present and absent respectively, we can use data validation.

Select any cell, go to data in ribbon and click on data validation. Select list from options and write A,P in the text box.

Hit OK.

Copy this validation for the whole open range of data (open range means cell where user can insert values).

Step 3: Lock all cells except where attendance needs to be entered.

Select date a date column. For example, select 1-Jan. Right now click on the selected range and go to the cell formatting. Go to protection. Uncheck the locked checkbox. Hit OK. Now copy this range to all open date ranges.

This will allow entry into these cells only when we protect the worksheets using worksheets protection menu. Thus your formulas, fomattings will be intact and users can only modify their attendance.

Step 4: Calculate Present Days of Teammates

So how do you calculate the present days? Well everyone has their own formulas for calculating attendance. I will discuss mine here. You can make changes as per your attendance sheet requirement.

I count the total number of present days as total days in a month, minus the number of days absent. This will keep holidays and weekends in check. They will automatically be counted as working days.

So the excel formula for counting present days will be like:

=COUNT(dates)-COUNTIF(attendance_range, “A”)

This will by default keep everyone present for the whole month until you have marked them absent on the sheet.

In the example, the formula is:

=COUNT($C$2:$AG$2)-COUNTIF(C3:AG3,”A”

Step 5: Protect the Sheet

Now that we have done everything on this sheet. Let’s protect it so that no one can alter the formula or the formatting on the sheet.

Go to the review tab in the ribbon. Find the Protect Sheet menu. Click on it. It will open a dialog box that will ask for the permissions you want to give to the users. Check all the permissions you would like to allow. I only want the user to be able to fill attendance with nothing else. So I am gonna keep it as it is.

You should use a password that you can remember easily. Otherwise, anyone can unlock it and alter the attendance workbook.

Step 6: Do the above procedure for all the month sheets

Do the same thing for each month sheet. The best way is to copy the same sheet and make 12 sheets out of it. Unprotect them and make the necessary changes and then protect them again.

Prepare the Master Attendance Sheet

Although we have all the sheets ready to be used for attendance filling, we don’t have one place to monitor them all.

Step 7: Prepare Master Table To Monitor Attendance at one place in Excel

For that, prepare a table that contains the name of team mates as row headings and name of month as column headings. See the image below.

Step 7: Lookup Attendance of Team From Each Month Sheet

To look up attendance from the sheet we can have a simple VLOOKUP formula but then we will have to do it 12 times for each sheet. But you know that we can have one formula to look up from multiple sheets.

Use this formula in Cell C3 and copy in the rest of the sheets.

=VLOOKUP($A3,INDIRECT(C$2&”!$A$3:$B$12″),2,0)

Step 8: Use the Sum function to get all the present days of the year of a team mate.

This is optional. If you like you can calculate the total present days of your employees throughout the year by simply using the sum formula.

Microsoft Excel

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA).

resource: wikipedia