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.
- 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).