Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     

Home > Exploring Excel

Rounding Values Up in Excel


"I want to enter a formula in cell J5 that will mark up a price in J5 by 1.8%. And I want the result to be round up to the nearest dollar." -- A.

A recent version of Excel introduced the CEILING function, which 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:

How to Round Up with Excel's INT and ROUNDUP Functions
How to Round Excel Time To Half-Hour Increments

All the best,

Charley

 
 
 
 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

Excel Dashboards

Create professional quality dashboard reports with Excel.