Turn cents into amounts, flag part cents

Turn 12345 cents into 123.45, and flag 123.5 or text for a look.

What you'll make

In Excel: 12345 cents becomes 123.45.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1AND(ISNUMBER(A8),A8=INT(A8))Checks the cell is a number with no part cents.TRUE
2A8/100Divides by 100 to turn cents into an amount.123.45
3"Review: not whole cents"The message when the check fails.Not used for row 8
4IF( … )Uses part 2 when the check passes, otherwise part 3.123.45
5IFERROR( … ,"Review: not whole cents")Shows the same message if the cell holds an error.123.45

Excel works from the inside out. Minus amounts and zero are fine.

=IFERROR(IF(AND(ISNUMBER(A8),A8=INT(A8)),A8/100,"Review: not whole cents"),"Review: not whole cents")

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 cent counts into the green column of the Template tab. The amounts fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only when your source sends whole cents in one currency. It only divides by 100.

Microsoft's help: IS functions