Show a service ratio with both units

Write 20 half-day visits per 1 month, not a bare count of 20.

What you'll make

In Excel: 20 becomes 20 half-day visits per 1 month.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(NOT(ISNUMBER(A8)), … )Checks for a missing amount or unit.FALSE
2"Review: service ratio inputs incomplete"What the cell says when part 1 is TRUE.Not used for row 8
3IF(C8<=0, … ,Flags a denominator of 0 or less.Not used for row 8
4A8&" "&B8&" per "&C8&" "&D8Writes both amounts with their units."20 half-day … 1 month"
5IF( … )Uses part 2, part 3 or part 4."20 half-day … 1 month"

Read it left to right. It shows the amounts as given and never converts the units.

=IF(OR(NOT(ISNUMBER(A8)),B8="",NOT(ISNUMBER(C8)),D8=""),"Review: service ratio inputs incomplete",IF(C8<=0,"Review: denominator 0 or less",A8&" "&B8&" per "&C8&" "&D8))

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 ratios. Paste each ratio's two amounts and two units into the green columns of the Template tab. The ratio fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Paste the amounts as numbers. This only shows what the request says; it doesn't schedule anything.