Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sorting whole numbers
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 -- wstruse ------------------------------------------------------------------------ wstruse's Profile: http://www.excelforum.com/member.php...o&userid=25071 View this thread: http://www.excelforum.com/showthread...hreadid=385897 |
#2
|
|||
|
|||
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. In article , wstruse wrote: 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 |
#3
|
|||
|
|||
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 -- wstruse ------------------------------------------------------------------------ wstruse's Profile: http://www.excelforum.com/member.php...o&userid=25071 View this thread: http://www.excelforum.com/showthread...hreadid=385897 |
#4
|
|||
|
|||
You could also use
=INT(A1)+A1 or =MOD(A1,1)=0 -- HTH RP (remove nothere from the email address if mailing direct) "wstruse" wrote in message ... 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 -- wstruse ------------------------------------------------------------------------ wstruse's Profile: http://www.excelforum.com/member.php...o&userid=25071 View this thread: http://www.excelforum.com/showthread...hreadid=385897 |
#5
|
|||
|
|||
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. In article , "Bob Phillips" wrote: You could also use =INT(A1)+A1 or =MOD(A1,1)=0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Sorting Numbers | Excel Discussion (Misc queries) | |||
sorting numbers more than one decimal | Excel Worksheet Functions | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) |