Prorate an amount by covered units
Turn 90.00 for 10 of 30 days into 30.00, using the counts your source gives.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | C8=0 | Checks whether the total units are 0. | No, it's 30 |
| 2 | "Review zero proration total" | The flag when the total is 0. | Not used for row 8 |
| 3 | ROUND(A8*B8/C8,2) | Amount times covered, divided by total, to the cent. | 30 |
| 4 | IF( … ) | Flags a zero total, otherwise uses part 3. | 30.00 |
Read it left to right. Units can be days, months or anything your source counts. The amounts are made-up.
=IF(C8=0,"Review zero proration total",ROUND(A8*B8/C8,2))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own amounts. Paste amounts, covered units and total units into columns A, B and C of the Template tab.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use the day or unit counts your source gives. Don't assume 30 days in a month.
Microsoft's help: ROUND function
OTHER ROUTES YOU MIGHT LIKE

Round amounts down to set decimals
-12.345 to 2 decimals → -12.35
Get the template →
Calculate a line amount to the cent
12.345 × 3 → 37.04
Get the template →
Convert amounts with a currency rate
125.5 × 0.9215 to 2 decimals → 115.65
Get the template →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →