For business users of Microsoft Excel Free guides and templates
Home >  Excel Help Questions > 

Rounding Methods

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.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

"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." -- A.

Excel's CEILING function offers one approach. It takes this form:

CEILING(number,significance)

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:

J5:  =CEILING(1.018*K5,1)

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:

=-INT(-number)

In this case, your formula would be:

J5:  =-INT(-1.018*K5)

You might also want to check the Related articles listed at the top-right of this page.

 

How to Use Excel's Better-Known Rounding Functions: INT, ROUNDUP, ROUNDDOWN, & ROUND

How to Round Excel Time To Half-Hour Increments

Overview of Excel's Rounding Functions

Free Excel Dashboards


Charley's SwipeFile charts