      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   # 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:

All the best,

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