ExcelUser logo Free guides and templates
Work In Progress...After nearly ten years, I'm redesigning ExcelUser.com. This is the new design. You can learn more here. Also, if you find something wrong with the site, please tell me about the problems. And thanks for your patience.--Charley Kyd
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.


Charley Kyd is a Microsoft Excel MVP by 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." -- 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




Dashboard Reporting With Excel


Charley's SwipeFile charts