CEILING.MATH function

CEILING.MATH rounds numbers up to the nearest multiple of a given significance value, useful for conservative cost estimates and price rounding.

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

Generate a CEILING.MATH formula

Describe what you need. The generator will reach for CEILING.MATH where CEILING.MATH is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How CEILING.MATH reads its arguments
numberrequiredsignificanceoptionalmodeoptionalCEILING.MATH →
ArgumentRequiredDescription
numberRequiredRequired numeric value to round up. Text or logical values cause #VALUE! error.
significanceOptionalOptional, defaults to 1. The multiple to round to; must be positive. Zero or mismatched sign produces #NUM! error.
modeOptionalOptional, defaults to 0. Controls negative-number rounding: 0 rounds away from zero, 1 rounds toward zero.

Returns

Returns a number rounded up to the nearest multiple of significance.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Round up unit price to nearest dollar for cost analysis

Order IDRepUnit Price
1001Alice9.73
1002Bob15.00
1003Carol22.75
=CEILING.MATH(C2, 1)

Result: 10

The unit price of $9.73 for order 1001 (Alice's item) rounds up to the next whole dollar ($10). This conservative rounding is useful for cost forecasts in purchasing or inventory planning, where overestimating costs is safer than underestimating.

2. Round up order total to nearest $10 for invoice batching

Order IDUnitsUnit Price
1001129.73
10022515.00
=CEILING.MATH(B2*C2, 10)

Result: 120

Order 1001 contains 12 units at $9.73 each, totaling $116.76. Rounding up to the nearest $10 increment yields $120. This pricing strategy groups orders into standard increments for batch processing, shipping tiers, or volume discounts.

3. Round up average order value to nearest $50 for revenue forecasting

Order IDOrder Total
1001116.76
1002375.00
1003182.00
1004236.25
1005603.90
=CEILING.MATH(AVERAGE(B2:B6), 50)

Result: 350

The average of the five order totals is $302.78. Rounding up to the nearest $50 increment yields $350, providing a conservative revenue estimate for quarterly budgeting and financial planning based on historical sales.

Common errors

Which CEILING.MATH error are you seeing?
CEILING.MATH returned an error#VALUE!
Remove quotes around numeric literals or use VALUE() to convert text: =CEILING.MATH(VALUE(C2), 1).
#NUM!
Use a positive significance value such as 1, 0.01, 0.05, or 10 depending on your rounding needs.
#NUM!
Ensure significance and number have the same sign, or use mode=1 to control rounding direction for negative numbers.
ErrorWhy it happensHow to fix it
#VALUE!Argument is text instead of a numeric value, such as =CEILING.MATH("9.73", 1) or =CEILING.MATH(B2, "dollar").Remove quotes around numeric literals or use VALUE() to convert text: =CEILING.MATH(VALUE(C2), 1).
#NUM!Significance argument is zero, which is undefined for rounding: =CEILING.MATH(9.73, 0).Use a positive significance value such as 1, 0.01, 0.05, or 10 depending on your rounding needs.
#NUM!Number and significance have opposite signs without proper mode handling, such as =CEILING.MATH(9.73, -1) or =CEILING.MATH(-9.73, 1).Ensure significance and number have the same sign, or use mode=1 to control rounding direction for negative numbers.

Tips and when to use something else

  • Use CEILING.MATH instead of the legacy CEILING function for support of negative numbers and the mode parameter, which gives you finer control over rounding direction.
  • The mode parameter (0 or 1) only affects negative numbers: mode=0 rounds away from zero (default), mode=1 rounds toward zero. For positive numbers, both behave identically.
  • Combine CEILING.MATH with SUMPRODUCT or array formulas to round multiple values in a single calculation without helper columns.
  • For rounding DOWN to the nearest multiple instead of up, use FLOOR.MATH with the same significance value, such as =FLOOR.MATH(9.73, 1).

Related functions

FLOOR.MATHRounds a number down to the nearest multiple of a specified significance value, commonly used for budget categories and interval-based rounding.ROUNDUPRounds a number up to a specified number of decimal places, always rounding away from zero, useful for conservative financial estimates.MROUNDMROUND rounds a number to the nearest multiple of a specified value, useful for rounding to standard increments like 5, 10, or 100.

Frequently asked questions

What's the difference between CEILING.MATH and the CEILING function?
CEILING.MATH supports negative numbers and includes a mode parameter for controlling rounding direction. The legacy CEILING function lacks these features and may return #NUM! errors with negative values. Use CEILING.MATH for modern spreadsheets.
When should I use mode=1 instead of the default mode=0?
Use mode=1 when rounding negative numbers toward zero (e.g., -9.5 becomes -9.0 instead of -10.0). Mode=0 rounds away from zero, which is the typical ceiling behavior. For positive numbers, both modes behave identically.
Can CEILING.MATH round to decimals or does it only work with whole numbers?
CEILING.MATH rounds to any significance value you specify. For example, =CEILING.MATH(9.73, 0.25) rounds to the nearest quarter-dollar ($9.75), while =CEILING.MATH(9.73, 0.01) rounds to the nearest cent. The result's precision depends entirely on your significance parameter.
How do I round up currency to the nearest half-dollar or quarter-dollar?
Use CEILING.MATH with the appropriate significance: =CEILING.MATH(9.73, 0.50) returns 10.00 (nearest half-dollar), and =CEILING.MATH(9.73, 0.25) returns 9.75 (nearest quarter-dollar). This is useful for pricing strategies, shipping costs, or bulk discounts.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17