Round Numbers

February 22, 2022 - by Bill Jelen

Problem: My formula is producing results with many decimal places. I need to round to the nearest cent or nearest dollar or even to the nearest hundred dollars.

Strategy: Use the versatile ROUND function. The function requires a number to be rounded then a precision value. If you use =ROUND(B2,2) you will round numbers to the nearest penny. If you use =ROUND(B2,0) you will round to the nearest dollar. The precision argument can be negative to indicate that you want to round to the left of the decimal point. If you use =ROUND(B2,-2) you will round to the nearest hundred dollars.

the ROUND function takes number of digits as the second argument. If you round to 2 digits, it rounds to the penny. Round to 0 digits and it rounds to the dollar. But the cool thing: If you round to -2 digits, it will round to the nearest 100! — Figure 344. Round to the nearest penny, dollar, or hundred.

ROUND can use any number as the precision argument. Although the figure above shows 2, 0, and -2, you could carry this logic forward. To round to the nearest million, use a precision of -6. To round to the nearest thousandth, use a precision of 3.

Additional Details: If you always want to round up or round down, use ROUNDUP or ROUNDDOWN functions. They work just like ROUND, requiring the number to round and the precision. Note that ROUNDUP will round away from zero. This makes sense for positive numbers, the ROUNDUP(1.01,0) will be 2. For negative numbers, the ROUNDUP(-1.01,0) will be -2. This is tricky, since -2 is actually lower than -1.01. If you want -1.01 to round to -1, then use =CEILING(1.01,1).

This article is an excerpt from Power Excel With MrExcel

Title photo by Joey Huang on Unsplash