Summary

To calculate the percentage of students absent in a given class, you can use a formula that calculates the number of students absent and then divides this number by the total number of students.  In the example shown, the formula in E5, copied down, is:

``````=(C5-D5)/C5
``````

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

Generic formula

``=(total-attended)/total``

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 the 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 with a percent sign (%), apply Percentage number format.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.