Write negative mixed numbers like -1 3/4

Turn -7 over 4 into -1 3/4, with one minus sign in front.

What you'll make

In Excel: -7/4 becomes -1 3/4.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1ABS(A8)Drops the minus sign for now. LET calls it n.7
2QUOTIENT(n,d)How many whole times 4 fits in 7. LET calls it w.1
3MOD(n,d)What is left over. LET calls it r.3
4IF(A8<0,"-","")&IF( … )Puts back the minus sign, then writes the mixed number."-1 3/4"
5LET( … )Names each step so the formula reads in order."-1 3/4"

d is the bottom number. GCD(r,d), called g, reduces the leftover, so 2/4 shows as 1/2.

=LET(n,ABS(A8),d,B8,w,QUOTIENT(n,d),r,MOD(n,d),g,GCD(r,d),IF(A8<0,"-","")&IF(r=0,w&"",IF(w=0,"",w&" ")&r/g&"/"&d/g))

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 numbers. Paste the top numbers into column A and the bottom numbers into column B of the Template tab. The mixed numbers fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Put any minus sign on the top number, and keep the bottom number above zero. Use whole numbers only.

Microsoft's help: GCD function