Split a Number into Whole Units and Remainder

Excel Formulas › Math

All versionsQUOTIENTMOD

To break a total into whole groups plus what’s left over — eggs into dozens, minutes into hours and minutes, items into full boxes — pair QUOTIENT (the whole part) with MOD (the remainder).

Quick formula: to split A2 into whole dozens and leftover units:
=QUOTIENT(A2, 12) // whole dozens =MOD(A2, 12) // leftover units
QUOTIENT keeps only the whole result of the division; MOD keeps only the remainder. Together they account for everything.

Functions used (tap for the full reference guide):

QUOTIENTMODINT

The example

Eggs split into dozens and leftovers.

ABC
1EggsDozensLeftover
23026
32521
44840

The formula

Whole dozens and leftover units:

=QUOTIENT(A2, 12) → dozens =MOD(A2, 12) → leftover // 30 → 2 dozen + 6

How it works

Two halves of one division:

  1. QUOTIENT(A2, 12) returns just the whole number of times 12 fits into A2 — the full dozens. For 30 that’s 2.
  2. MOD(A2, 12) returns the remainder — the eggs left over after the full dozens. For 30 that’s 6.
  3. They always reconcile: QUOTIENT × 12 + MOD = A2.
  4. QUOTIENT(A2, 12) equals INT(A2/12) for positive numbers — either works.

Minutes to h:mm: =QUOTIENT(A2,60) & "h " & MOD(A2,60) & "m" turns 150 minutes into “2h 30m.” The same pattern handles any “big units + small units” split.

Try it: interactive demo

Live demo

Split a total into whole groups and remainder.

Whole:   Remainder:

Variations

Minutes to hours + minutes

A readable duration:

=QUOTIENT(A2,60) & "h " & MOD(A2,60) & "m"

INT alternative

Same whole part for positives:

=INT(A2/12)

Boxes needed (round up)

If a partial group still needs a box:

=ROUNDUP(A2/12, 0)

Pitfalls & errors

#DIV/0! with a zero divisor. Both QUOTIENT and MOD need a non-zero divisor.

QUOTIENT vs INT on negatives. They differ for negative numbers (QUOTIENT truncates toward zero, INT rounds down). For positive counts they agree.

“Whole groups” vs “groups needed.” QUOTIENT gives complete groups; if a leftover still requires a full container, use ROUNDUP instead.

Practice workbook

📊
Download the free Split a Number into Whole Units and Remainder practice workbook
Totals split with live QUOTIENT/MOD, the minutes-to-h:mm and round-up variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I split a number into whole units and a remainder in Excel?
Use QUOTIENT for the whole part and MOD for the remainder: =QUOTIENT(30, 12) is 2 dozens and =MOD(30, 12) is 6 leftover.
How do I convert minutes into hours and minutes?
Combine them: =QUOTIENT(A2,60)&"h "&MOD(A2,60)&"m" turns 150 into "2h 30m".
What's the difference between QUOTIENT and INT?
For positive numbers they give the same whole result. For negatives, QUOTIENT truncates toward zero while INT rounds down.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Remainders & cycles (MOD) · Round up or down · Calculate time between two times

Function references: QUOTIENT · MOD · INT