Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

8 Answers 8

Try this formula in B1

=INT(A1)

share|improve this answer
add comment

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

share|improve this answer
add comment

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

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
add comment

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

share|improve this answer
1  
Adding some explanation of why this answers the question would make this a better answer. –  ChrisF Jan 31 '13 at 23:21
add comment

Use the TRUNC function.

i.e. B1 =TRUNC(A1)

share|improve this answer
1  
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
add comment

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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.