I have a list of numbers in column A that go to several decimal places (e.g. A1 is 27.34
). Is there a way to fill column B with the same values as column A, but rounding to the nearest integer?
8 Answers
excel has a floor() funcion
floor()
The syntax for the FLOOR function is:
FLOOR( number, significance )
number is the number that you wish to round down.
significance is the multiple of significance that you wish to round a number to.
Another way, without using functions, is to use the Decrease Decimal button from the Number group in the Home tab.
-
2This will only change the display of the number, not the value in the cell, though.– teylynJan 31, 2013 at 23:06
FLOOR
and ROUND( <cellreference> , 0)
will have different behavior for negative numbers.
ROUND(-27.3, 0)
will give you -27. But the highest integer that's less than -27.3 will be -28.
For positive numbers, FLOOR
is also only going to provide you with the nearest integer if the portion after the decimal place is less than 0.5; otherwise ROUND ( <cellreference>, 0)
will be better to use if you want the nearest integer in all situations.
TRUNC
just gets rid of everything after the decimal place; but again, for positive numbers, that won't give you the closest integer all the time, only for numbers where the non-integer part is less than 0.5.
Try =FLOOR(A1,1)
in cell B1 :-)
-
1Adding some explanation of why this answers the question would make this a better answer.– ChrisFJan 31, 2013 at 23:21
Use the TRUNC function.
i.e. B1 =TRUNC(A1)
-
1
-
Click cell B1 , type TRUNC(
, then click cell A1, add a )
and press enter. This should put the correct number in B2, which you can then drag the corner of to cell B100 to fill every cell with the proper formula.
I would use the RoundDown function, as it is designed specifically for what you are asking.
It takes two arguments, the first being the number [or cell reference] you want to round down, the second being the amount of decimals it should have.
Examples:
=ROUNDDOWN(3.1456,0) becomes 3
=ROUNDDOWN(3.1456,1) becomes 3.1
=ROUNDDOWN(3.1456,2) becomes 3.14
=ROUNDDOWN(3.1456,3) becomes 3.145
=ROUNDDOWN(3.1456,4) becomes 3.1456