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

In Excel: 90.00 for 10 of 30 units becomes 30.00.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1C8=0Checks 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
3ROUND(A8*B8/C8,2)Amount times covered, divided by total, to the cent.30
4IF( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own amounts. Paste amounts, covered units and total units into columns A, B and C of the Template tab.
  3. 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