sorting whole numbers

W

wstruse

Could someone tell me how to sort out the whole numbers in a column of
mixed numbers? (decimals and whole numbers )

Thank you in advance,

wstruse
 
J

JE McGimpsey

One way:

Assuming your numbers are in column A, in a free column, enter

=ABS((A1-ROUND(A1,0))) < 1E-10

and copy down. Sort on this column. The TRUE values are whole numbers.

Adjust 1E-10 to be smaller than the precision of your values (e.g., if
your values have a maximum of 2 places after the decimal point, you
could, if you wanted to, use 1E-04 instead.
 
W

wstruse

JE McGimpsey,

That worked great. I have over 800,000 cells to look through and you
just saved me many hours of time.

Thank you,

William Struse
 
B

Bob Phillips

You could also use

=INT(A1)+A1

or

=MOD(A1,1)=0

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

JE McGimpsey

Don't see how

=INT(A1)+A1

would work.

The only problem with MOD(A1,1)=0 is that, for calculated values, a
small rounding error gives a false negative.
 
Top