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
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
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 <[email protected]>,
wstruse <[email protected]> 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
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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
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 <[email protected]>,
wstruse <[email protected]> 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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
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 <[email protected]>,
wstruse <[email protected]> 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
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <[email protected]>,
wstruse <[email protected]> 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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <[email protected]>,
wstruse <[email protected]> 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
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 <[email protected]>,
wstruse <[email protected]> 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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
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 <[email protected]>,
wstruse <[email protected]> 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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <[email protected]>,
wstruse <[email protected]> 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
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <[email protected]>,
wstruse <[email protected]> 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
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 <[email protected]>,
wstruse <[email protected]> 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
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <[email protected]>,
wstruse <[email protected]> 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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
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 <[email protected]>,
wstruse <[email protected]> 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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
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 <[email protected]>,
wstruse <[email protected]> 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
You could also use
=INT(A1)+A1
or
=MOD(A1,1)=0
--
HTH
RP
(remove nothere from the email address if mailing direct)
"wstruse" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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 <e#[email protected]>,
"Bob Phillips" <[email protected]> wrote:
> You could also use
>
> =INT(A1)+A1
>
> or
>
> =MOD(A1,1)=0
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks