# How to calculate percent of goal when lower is better in Excel

Divide the target by the actual instead: `=B2/C2`. Beating a 4-hour target with 5 hours scores 80%.

**Excel:** `=B2/C2` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Metric | Target | Actual | Score |
| Response time (h) | 4 | 5 |  |
| Cost per lead ($) | 50 | 40 |  |
| Defect rate | 0.02 | 0.025 |  |
| Handle time (min) | 6 | 6 |  |
| Downtime (h) | 10 | 8 |  |

Formula in D2, filled to D6: `=B2/C2`

Result: 80%; 125%; 80%; 100%; 125%

## How it works

For metrics where less is better, actual/target rewards doing worse. Flipping the division, target/actual, gives 100% on target, more than 100% when the actual is lower, and less when it is higher. Spending $40 per lead against a $50 target scores 125%.

## Other ways

- `=1-(C2-B2)/B2`: Another convention: 100% minus the percentage over target. It changes linearly, so 25% over target scores 75%. (Scores response time 75% instead of 80%.)

## Common mistakes

- An actual of 0 (no downtime at all) makes target/actual divide by zero. Cap it: `=IF(C2=0,1.5,MIN(B2/C2,1.5))`.
- Mixing scoring rules in one scorecard makes the scores incomparable; use one convention for all lower-is-better metrics.

## Questions

**How do I cap the score at 120%?**

Wrap it in MIN: `=MIN(B2/C2,1.2)`.

**How do I score higher-is-better metrics?**

Use actual divided by target: `=C2/B2`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percent-of-goal-when-lower-is-better