1

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?

1
  • cpast's edit changed the meaning of the question.
    – wilson
    Feb 4, 2013 at 11:38

8 Answers 8

3

Try this formula in B1

=INT(A1)

1

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

1

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

enter image description here

1
  • 2
    This will only change the display of the number, not the value in the cell, though.
    – teylyn
    Jan 31, 2013 at 23:06
1

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.

0

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

1
  • 1
    Adding some explanation of why this answers the question would make this a better answer.
    – ChrisF
    Jan 31, 2013 at 23:21
0

Use the TRUNC function.

i.e. B1 =TRUNC(A1)

2
  • 1
    An explanation of why this works would make this a better answer.
    – ChrisF
    Jan 31, 2013 at 23:23
  • Maybe include what's happening (why this works)?
    – Everett
    Jan 31, 2013 at 23:32
0

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.

1
  • Darn, you guys type fast... Jan 31, 2013 at 22:40
0

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

You must log in to answer this question.

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