Calculation Issues

How to Calculate Attendance Percentage from Two Sums in Excel

Natalie TaylorNatalie Taylor Nov 3, 2026 870 views

Question details

The user wants to find a cumulative attendance percentage by dividing a sum of actual attendance by a sum of total possible attendance.

How to Calculate Attendance Percentage from Two Sums in Excel
Product
Excel
Device & OS
not provided
Scenario
Tracking and calculating cumulative attendance records where the final ratio needs to be displayed as a correct percentage.
Observed behavior
The goal is to calculate the division of two summed ranges and correctly format the resulting decimal as a percentage with specific decimal places, rather than manually multiplying the result by 100.
Before you start

Ensure your attendance data is organized properly, with actual attendance counts in one column or row, and the total possible counts in another.

Solution 1Recommended

Divide Sums and Apply Percentage Formatting

Use a formula to divide the sum of actual attendance by the sum of possible attendance, then use Excel's native cell formatting to display it as a percentage.

Instead of mathematically multiplying your division result by 100, the best practice in Excel is to calculate the raw decimal ratio and apply a Percentage number format. This keeps the underlying data accurate for future calculations.

1
Enter the formula

Select an empty cell for your result and type the formula =SUM(B2:B5)/SUM(C2:C5), replacing the cell references with the actual ranges for your attendance data and total possible counts.

2
Calculate the decimal ratio

Press Enter to execute the formula. Excel will return a decimal value, such as 0.941176.

3
Apply percentage format

Click on the result cell to select it. Navigate to the Home tab on the ribbon and click the Percentage Style (%) icon located in the Number group.

4
Adjust decimal places

To specify the accuracy (like 94.12%), click the 'Increase Decimal' or 'Decrease Decimal' buttons next to the Percentage icon on the Home tab until you reach two decimal places.

Divide Sums and Apply Percentage Formatting
Using Absolute References: If you need to copy this formula to other cells while referencing the exact same totals, use absolute references by adding dollar signs to your ranges (e.g., $B$2:$B$5).
Simplify Your Spreadsheets

Calculate Percentages Easily with WPS Spreadsheet

WPS Office offers a fully compatible and intuitive spreadsheet application that makes complex formula calculations and percentage formatting incredibly straightforward.

  1. 1. Open your data: Launch WPS Spreadsheet and open your attendance tracking workbook.
  2. 2. Input the division formula: Select the target cell and type =SUM(Range1)/SUM(Range2), using your actual data ranges.
  3. 3. Execute calculation: Press Enter on your keyboard to reveal the decimal calculation result.
  4. 4. Format instantly: Go to the Home tab and click the '%' icon to format the decimal as a percentage instantly.
Easily calculate cumulative attendance percentages using standard SUM formulas.Fully compatible with Microsoft Excel (.xlsx) formats and functions.Quick one-click percentage formatting right from the Home ribbon.Lightweight, fast, and free alternative for all everyday office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my attendance percentage showing as a decimal instead of a percentage?

By default, dividing two numbers in Excel produces a raw decimal (e.g., 0.94 instead of 94%). You must explicitly format the cell as a Percentage from the Home tab to display it with a percent sign, which eliminates the need to multiply your formula by 100.

How do I copy the formula without shifting my total ranges?

You need to use absolute references. Add dollar signs to your cell range coordinates (for example, =SUM($B$2:$B$5)/SUM($C$2:$C$5)). This locks the specified cells so they do not shift relative to the new location when you drag the formula down or across.

Can I calculate the sum if some attendance cells are blank?

Yes, the SUM function automatically ignores blank cells and text strings when adding numbers. However, ensure that the divisor (your total possible count) evaluates to a number greater than zero, otherwise Excel will return a #DIV/0! error.