How to Round Values Up in Excel
Using Excel's CEILING and INT Functions
Excel offers several functions for rounding numbers upwward. Here are two methods.
Charley Kyd, MBA
Microsoft Excel MVP
The Father of Spreadsheet Dashboards
"I want to enter a formula in cell J5 that will mark up a price in K5
by 1.8%. And I want the result to be rounded up to the nearest dollar." --
Excel's CEILING function
offers one approach. It takes this form:
Here, number is the number to be rounded
away from zero. And significance is the nearest multiple. If you
want to round to the nearest nickel, for example, the significance would
be .05. If you want to round to the nearest dollar, the significance
would be 1.
So here's your formula for the cell shown:
If you use an earlier version of Excel that
doesn't have CEILING, use the
INT function. This function rounds to the
nearest whole number by moving downward. That is, INT(3.9)= 3 and INT(-3.9)=-4.
Therefore, to round a positive number upward,
turn the number minus, find the INT and then turn the number positive
again. That is, use:
In this case, your formula would be:
You might also want to check the Related articles listed at
the top-right of this page.