# Rounding in Excel

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?

-
cpast's edit changed the meaning of the question. – wilson Feb 4 '13 at 11:38

Try this formula in B1

`=INT(A1)`

-

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.

http://www.techonthenet.com/excel/formulas/floor.php

-

Another way, without using functions, is to use the Decrease Decimal button from the Number group in the Home tab.

-
This will only change the display of the number, not the value in the cell, though. – teylyn Jan 31 '13 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 :-)

-
Adding some explanation of why this answers the question would make this a better answer. – ChrisF Jan 31 '13 at 23:21

Use the TRUNC function.

i.e. B1 =TRUNC(A1)

-
An explanation of why this works would make this a better answer. – ChrisF Jan 31 '13 at 23:23
Maybe include what's happening (why this works)? – Everett Jan 31 '13 at 23:32

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.

-
Darn, you guys type fast... – Marcus Chan Jan 31 '13 at 22:40

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

-