Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
cpast's edit changed the meaning of the question. – wilson Feb 4 '13 at 11:38

Try this formula in B1


share|improve this answer

excel has a floor() funcion


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.

share|improve this answer

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

enter image description here

share|improve this answer
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.

share|improve this answer

Try =FLOOR(A1,1) in cell B1 :-)

share|improve this answer
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)

share|improve this answer
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.

share|improve this answer
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.


=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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .