Technology, made understandable.Our approach
Software & workThe number after out of

Make spreadsheet percentages auditable with clear denominators

Expose the counts behind a spreadsheet percentage, account for unknown outcomes, define filter behaviour and combine groups without averaging away their size.

The number after “out of” is part of the result

“Completion is 80%” leaves the important question unanswered: 80% of which requests? All requests received, only eligible requests, or only those with a recorded outcome? A percentage becomes auditable when another person can identify its population, reproduce both counts and explain each exclusion.

This explainer uses Excel for Microsoft 365 desktop, English function names and comma-separated formula arguments. Work in a saved copy or a blank workbook. All request records and results below are hypothetical worked examples, not measurements or live spreadsheet tests. The method concerns a proportion of records; percentages describing price changes or budget variance need different definitions.

A useful result has four visible parts: numerator, denominator, rate and scope. For example: “4 completed / 6 eligible requests = 66.7%; October intake, status recorded through October 31; one outcome unknown.” Keeping those parts together prevents a polished percentage from concealing an uncertain population.

Write the denominator sentence before the formula

Imagine an equipment team reporting on eight October requests. Its agreed question is: “What proportion of eligible October requests were completed by October 31?” Each row represents one request, even if that request contains several items. A request for ten chairs still counts once.

The denominator sentence is: “Include every unique October request marked eligible, including requests whose completion status is unknown; exclude cancelled and out-of-scope requests.” This policy is an example, not a universal reporting rule. Resolve disagreements about eligibility before calculating the rate.

Hypothetical input: enter these records in A1:D9
Request IDScope noteEligibleCompleted
R101October intakeYesYes
R102October intakeYesYes
R103October intakeYesNo
R104October intakeYesUnknown
R105CancelledNoNo
R106October intakeYesYes
R107Out of scopeNoYes
R108October intakeYesYes

R107 deliberately has a positive completion status but is outside the population. It must not enter the numerator. This is a useful audit record: a formula counting every “Yes” in Completed would answer a different question.

Before using real data, verify the reporting period against the original intake record, check that IDs are present and unique, and confirm that eligibility decisions have reasons. These formulas count rows; they cannot establish that two rows represent different requests. Preserve disputed records separately while resolving them rather than silently deleting them.

Two people checking documents and a laptop at a table.
Photo: Mikhail Nilov / Pexels · Pexels License. Resized for display; some views use a crop. Illustrative photograph.

Give the numerator and denominator their own cells

Place the labels Eligible requests, Completed eligible requests and Completion rate in G2:G4. Enter the following formulas in H2:H4, respectively:

=COUNTIFS(C2:C9,"Yes")
=COUNTIFS(C2:C9,"Yes",D2:D9,"Yes")
=IF(H2=0,"No eligible requests",H3/H2)

Microsoft documents that COUNTIFS counts positions meeting every supplied condition, and requires equally sized criteria ranges. Here the numerator repeats the eligibility condition and adds completion. The denominator does not depend on completion being filled in.

The expected counts are 6 in H2 and 4 in H3. H4 should evaluate to approximately 0.6667 before percentage formatting. Select H4 and use Home > Number > Percent Style. Microsoft’s percentage-format documentation explains that a ratio is stored as a decimal and displayed as a percentage. Do not also multiply the formula by 100. Choose enough displayed decimal places for the report, while retaining the unrounded ratio for subsequent calculations.

The zero-denominator branch uses the documented IF function. “No eligible requests” means the rate is undefined. Zero completed requests out of six eligible requests instead means 0%. Keep those states distinguishable. A downstream chart or calculation must explicitly accommodate the text branch.

Make unknown outcomes visible without changing the question

For this example, the six eligible requests split into four Yes, one No and one Unknown. Verify that those categories total six. If they do not, inspect blanks, unexpected spellings and eligibility entries before publishing. A missing outcome is not evidence of failure, but it is also not evidence of completion.

Excluding Unknown would produce 4/5 = 80%. That can be a useful secondary measure labelled “completion among requests with known outcomes.” It cannot replace the original 4/6 measure without changing the stated question. Report coverage too: five known outcomes out of six eligible requests, approximately 83.3%.

There is also an honest limit on interpretation. With one unknown outcome, final completion for this fixed six-request cohort could be anywhere from 4/6 to 5/6, or approximately 66.7%–83.3%, depending on that outcome. This is an accounting bound, not a statistical confidence interval. It assumes eligibility and the other five statuses are correct.

Decide whether filtering should change the denominator

The formulas above describe the specified ranges and criteria, regardless of which records you are inspecting. Hiding an unfinished row should not make this fixed-cohort report improve. If a report is intended to show only visible records, define that as a separate metric and make both counts follow the same visibility rule.

For a visible-record version, add numeric helper columns E and F. Give each eligible record 1 in E, otherwise 0; give each completed eligible record 1 in F, otherwise 0. Label them clearly. For this eight-record example, E should total six and F four before filtering.

=SUBTOTAL(109,E2:E9)
=SUBTOTAL(109,F2:F9)

These give the visible denominator and numerator. Microsoft documents that SUBTOTAL with function number 109 sums while excluding filtered-out and manually hidden rows. Divide these two results with the same zero-denominator check. Never combine a visible numerator with the fixed denominator unless that is explicitly the intended question.

As a hypothetical check, hiding R103 should leave the fixed report at 4/6, while this visible-record version becomes 4/5. Restore the row afterward. If the intended report changes unexpectedly, inspect its formula references and visibility policy before correcting the displayed value.

Combine groups through their counts

Suppose a separate hypothetical month has two non-overlapping teams: Team A completed 9 of 10 eligible requests, while Team B completed 1 of 2. Their rates are 90% and 50%. The average of those percentages is 70%; the combined request completion rate is 10/12, approximately 83.3%.

Use combined numerators divided by combined denominators when every request should carry equal weight. An average of team rates instead gives each team equal weight. That answers a different question. Before combining counts, check that teams use identical eligibility rules and that no request appears in both groups.

Do not infer a count from a rounded percentage when the underlying counts are available. Likewise, a change from 60% to 75% is 15 percentage points; its relative increase is 25%, using 60% as the baseline. Name the comparison so readers know which denominator applies.

Use a small release check, then preserve the explanation

In the working copy, temporarily change R107’s completion from Yes to No: the intended fixed result should remain 4/6. Change R104 from Unknown to Yes: it should become 5/6. Restore both entries. Temporarily make every eligibility entry No: the rate should show “No eligible requests.” Restore the original data before sharing.

If a check fails, recover the saved inputs and inspect the count formulas first. Check that their ranges cover the same records and repeat the eligibility condition. When adding rows beyond row 9, extend every relevant range and helper column together; an extra formatted row does not prove it is included.

Publish the rate beside its counts, scope sentence, unknown count and status cutoff. Keep the source snapshot and exclusion reasons with the workbook. A reviewer should be able to explain why each record belongs before trusting the percentage it produces.

Sources, versions & scope

The steps describe the documented controls and expected checks; they are not a claim that every device or software release has been physically tested. Platform and version differences are stated with the procedure.

  1. Microsoft: COUNTIFS function
  2. Microsoft: Format numbers as percentages in Excel
  3. Microsoft: IF function
  4. Microsoft: SUBTOTAL function
Share this guide