Contact
12 programs
Explore the site

CEILING.MATH in Google Sheets: Round to an Upper Multiple with Negative Modes

Math Intermediate Google Sheets

Use Sheets CEILING.MATH for pack capacity, explicit negative modes and quarter-unit upper thresholds.

Verification status: See the evidence and test scope described in this guide. How formula examples are checked

Quick answer: CEILING.MATH rounds to an upper integer or multiple. By default, negative values move toward zero; a nonzero mode makes them move away from zero. =CEILING.MATH(-18.4,3) is expected to return −18.

Availability and version history

  • Introduced: first launch date or introduction version not established by the official history reviewed.
  • Availability: documented in the rolling Google Sheets service; this guide uses ordinary cells in the web editor, without a claim about identical mobile authoring workflows.
  • Checked: October 14, 2026 against the official CEILING.MATH reference. Examples were independently calculated; no native Sheets execution was performed.

Syntax and defaults

=CEILING.MATH(number, [significance], [mode])

Only the number is required. Google documents a default significance of 1. For negative numbers, a zero or omitted mode selects toward-zero rounding; another mode selects away-from-zero rounding. The step examples here use positive significance. Official CEILING.MATH reference

Significance is the spacing between permitted results, not a count of decimal places. With a step of three, permitted results include …, −21, −18, −15, …, 0, 3, 6, … . The negative mode determines which side of an off-grid negative number is selected.

Example 1: default integer rounding

=CEILING.MATH(8.4)
=CEILING.MATH(-8.4)
=CEILING.MATH(8)

Expected results: 9, −8, 8. The negative fraction moves upward on the number line, closer to zero. A whole number is already a permitted result on the default unit grid.

A cell displayed with no decimals may still store a fraction. Rounding through this formula changes the returned number, whereas changing a display format alone leaves its stored value unchanged.

Example 2: use three-unit multiples

=CEILING.MATH(18.4,3)
=CEILING.MATH(-18.4,3)

Expected results: 21 and −18. For the positive input, the next multiple at or above 18.4 is 21. For the negative input, −18 is at or above −18.4.

An integer can still be between the permitted multiples: =CEILING.MATH(19,3) returns 21. Being a whole number is different from already being a multiple of the selected step.

Example 3: compare negative modes explicitly

=CEILING.MATH(-18.4,3,0)
=CEILING.MATH(-18.4,3,1)
=CEILING.MATH(-18.4,3,-1)

Expected results: −18, −21, −21. Both nonzero modes shown choose the away-from-zero negative direction. Google documents the zero-versus-nonzero distinction; the sign of the mode itself is not a decimal precision setting.

Keep a mode parameter visible when it represents a business rule. A returned value can be mathematically consistent and still be the wrong policy for a negative balance or adjustment.

Advanced example: capacity in four-unit packs

A fictional studio needs 9, 16, 17 cards for three jobs, entered in A2:A4. Cards are supplied in packs of four. In B2 enter and fill down:

=CEILING.MATH(A2,4)

Expected capacity: 12, 16, 20. In C2 enter =B2/4; expected pack counts are 3, 4, 5. In D2 enter =B2-A2; spare cards are 3, 0, 3.

Separately allocated capacity totals 48. If stock can be shared, =CEILING.MATH(SUM(A2:A4),4) gives 44, because total demand is 42. Rounding after pooling reduces this sample’s required pack count from 12 to 11.

The formula cannot determine whether sharing is allowed. Encode the actual grouping constraint rather than choosing the smaller result automatically.

Advanced example: an upper grid with a nonzero origin

Suppose allowed appointment offsets begin at 2 and increase in increments of 4. Use:

=2+CEILING.MATH(F2-2,4)

For numeric F2 values 3, 6, 7, −1, expected grid values are 6, 6, 10, 2. The formula subtracts the grid origin before rounding and restores it afterward.

The default negative mode is important when an input falls below the origin. For −1, subtracting two gives −3; its mathematical upper four-unit multiple is zero; adding two gives 2. Changing the mode would implement a different rule for those negative offsets.

Fractional steps and practical checks

=CEILING.MATH(9.1,0.25) is expected to give 9.25. For −9.1, omitted mode gives −9, while mode 1 gives −9.25. These examples use quarter-unit spacing and avoid a source value exactly at a decimal boundary.

Use ROUNDUP for a decimal-place count and away-from-zero direction, and ROUND for nearest rounding. They solve different problems from a chosen-multiple upper bound.

The checked Google page does not provide a complete text-coercion or extreme-magnitude error table. Validate editable steps and numeric inputs, keep the unrounded source and test boundary values in the actual workbook before relying on precision-sensitive thresholds.

Verification: Expected results use fictional data and independent calculations rather than native Google Sheets observations. See the editorial testing standards.