Thread: Sorting numbers doesn't work correctly

I rarely use Excel, but I need to sort a spreadsheet in which one of the
columns has only numbers. For example: 121.593, 121.58, 121.641, 121.61. My
expectation was that Sort Ascending would produce 121.58, 121.61, 121.593,
121.641. Instead, the sort produces the following sequence: 121.58, 121.593,
121.61, 121.641. In other words, it doesn't seem to understand that the
two-digit numbers (after the point (.)) go before the three digit numbers. Is
there a workaround?

121.61 is bigger than 121.593 therefore Excel correctly places it after 121.593 when sorting ascending,why should the number of decimal places make a difference?

To agree with both Bearacade and daddylonglegs, but in a different way:

Which would you rather have written on your paycheck:
$121.641 or $121.61
It is not the number of decimal places to the right of the decimal that
determine its magnitude it is the value of the number themselves based on
their position relative to the decimal point. Excel is doing this just fine.

Now, IF you were sorting a group of numbers being treated as text you might
see different results (for instance, 10 coming before 2), but that's an ASCII
sort and not a numeric one.

"GrammyEmmy" wrote:

> I rarely use Excel, but I need to sort a spreadsheet in which one of the
> columns has only numbers. For example: 121.593, 121.58, 121.641, 121.61. My
> expectation was that Sort Ascending would produce 121.58, 121.61, 121.593,
> 121.641. Instead, the sort produces the following sequence: 121.58, 121.593,
> 121.61, 121.641. In other words, it doesn't seem to understand that the
> two-digit numbers (after the point (.)) go before the three digit numbers. Is
> there a workaround?

Hi Grammy,
Select your column of numbers then do a find and replace using . as the
find what item and leave the replace box empty.
This will remove all your decimal points and sort ascending will now
stack the numbers the way you want.
To get the decimals back go to a blank column or insert a new temporary
column next to your original series. In the top row place this formula

=LEFT(A1,3)&"."&MID(A1,4,3)
Change the A1 to whatever your first cell is

Copy the cell down to the end of your data.
Select this entire column and edit copy.
Select your original column and paste special>values.
Delete your temporary column and your done.

Not the most elegant solution but it will work.

HTH
Martin

OK, I see where I wasn't clear. The front portion of these numerics is
separate, from the back portion. They are from the Code of Federal Regs for
the FAA and, for instance, 121.61 represents Part 121, Regulation 61. There
are also Part 119's and Part 125's, each with many, many regs. I'm not sure
how to restate the question succinctly, but an example of what would be OK to
end up with is this:

119.004
119.484
121.061
121.593
125.076
125.188

Thanks.
--
Monica
www.publishing-consultants.com


"daddylonglegs" wrote:

>
> 121.61 is bigger than 121.593 therefore Excel correctly places it after
> 121.593 when sorting ascending,why should the number of decimal places
> make a difference?
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=555151
>
>

Aha! In that case, I think what MartinW posted below will do the trick.
Might take a little tweaking. I presume if you have 119.4 then before
sorting the .4 portion should be converted to 3-characters (or max of any
..number entry) as 004.

"GrammyEmmy" wrote:

> OK, I see where I wasn't clear. The front portion of these numerics is
> separate, from the back portion. They are from the Code of Federal Regs for
> the FAA and, for instance, 121.61 represents Part 121, Regulation 61. There
> are also Part 119's and Part 125's, each with many, many regs. I'm not sure
> how to restate the question succinctly, but an example of what would be OK to
> end up with is this:
>
> 119.004
> 119.484
> 121.061
> 121.593
> 125.076
> 125.188
>
> Thanks.
> --
> Monica
> www.publishing-consultants.com
>
>
> "daddylonglegs" wrote:
>
> >
> > 121.61 is bigger than 121.593 therefore Excel correctly places it after
> > 121.593 when sorting ascending,why should the number of decimal places
> > make a difference?
> >
> >
> > --
> > daddylonglegs
> > ------------------------------------------------------------------------
> > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> > View this thread: http://www.excelforum.com/showthread...hreadid=555151
> >
> >

Martin,
That worked great. Thank you very much!
--
Monica
www.publishing-consultants.com


"MartinW" wrote:

> Hi Grammy,
> Select your column of numbers then do a find and replace using . as the
> find what item and leave the replace box empty.
> This will remove all your decimal points and sort ascending will now
> stack the numbers the way you want.
> To get the decimals back go to a blank column or insert a new temporary
> column next to your original series. In the top row place this formula
>
> =LEFT(A1,3)&"."&MID(A1,4,3)
> Change the A1 to whatever your first cell is
>
> Copy the cell down to the end of your data.
> Select this entire column and edit copy.
> Select your original column and paste special>values.
> Delete your temporary column and your done.
>
> Not the most elegant solution but it will work.
>
> HTH
> Martin
>
>
>

Glad I could help.

Regards
Martin