Exceljet

Quick, clean, and to the point

Percent of students absent

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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.