Rounding numbers doesn’t need to be difficult even when you are dealing with decimals. When you are using Excel if you don’t want unnecessary decimal places in cells because they cause ###### symbols to appear, or you don’t need accuracy down to the microscopic level, change the cell format to get the number of decimal places you want.
Learn more about how to round numbers up and down.
In case you want to round to the closest major unit, like thousands, hundreds, tens, or ones, use a function in a formula.
By using a button:
- You must choose the cells that you want to format. On the “home” tab, click option – “decrease decimal” or “increase decimal” to show fewer or more digits after the decimal point.
By applying a built-in number format:
- In the “number” group, on the “home” tab, click the arrow close to the list of “number formats”, and afterwards click “more number formats.”
- in the “category” list click “currency”, “accounting”, “percentage”, or “scientific.”
- In the “decimal” places area, enter the number of decimal places that you would like to display.
Discover what is the ROUND function.
By utilizing a function in a formula
If you want to round a number to the number of digits utilize the “round” function. This function has only two arguments.
For your information: arguments are constituents of data the formula needs to run.
- The 1st argument is the number you would like to round, that can be a number or a cell reference
- The 2nd argument is the number of digits you would like to round the number to.
Let’s say that cell B1 contains 722.8835. To round the number to the closest:
- Round to thousands:
- Type =ROUND(B1,-3) which equals 1000
- 722.8835 is closer to 1000 than to 0 (0 is a multiple of 1000)
- Utilize a negative number in this case, because you want the rounding to happen to the left of the decimal point. The same approach applies to the next two formulas that round to hundreds and tens.
Learn more about the ceiling. math function.
- Round to hundreds:
- Type =ROUND(B2,-2) which equals 700
- 700 is closer to 713.8425 than to 800.
- Round to tens:
- Type =ROUND(B1,-1) which equals 720
- Round to ones:
- Type =ROUND(B1,0) which equals 624
- Utilize a zero to round the number to the closest single digit.
- Round to tenths:
- Type =ROUND(B1,1) which equals 723.8
- Utilize a positive number in this case to round the number to the number of decimal points you determine. The same thing applies to the next two formulas that round to hundredths and thousandths.
- Round to hundredths:
- Type =ROUND(B1,2) which equals 723.68
Discover the hardware implementations and implications when rounding numbers.
- Thousandths:
- Type = ROUND(B1,3) which equals 723.683
Round a number up by utilizing the “roundup” function. It works just the same as “round”, only, it always rounds a number up. For instance, if you want to round 3.2 up to zero decimal places: =ROUNDUP(3.2,0) which equals 4
Round a number down by utilizing the “round down” function. It works just the same as “round”, only, it always rounds a number down. For instance, if you would like to round down 2.14159 to three decimal places: =ROUNDDOWN(2.14159,3) which equals 2.141
