How do I sort a column consisting of fractions, decimals, and who.

I need to sort products by the horsepower. Horsepower can be a fraction,
decimal or whole number. For example, I need 1/100 to come before 1/10,
1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before
1...etc.

Why don't you use the Fraction format under Number format?

That way you only enter the decimal value and it will be represented by it's
fraction, but the sorter won't be confused.

Another option is to use this formula:

=IF(ISERR(SEARCH("/",E8)),VALUE(E8),LEFT(E8,SEARCH("/",E8)-1)/RIGHT(E8,SEARCH("/",E8)-1))

Where E8 is the value you need to convert from fraction or decimal to
decimal. With this formula you can create another column next to the
fractions, and you can sort by it's decimal values...

E F
8 '1/2 .5
9 '1/3 .33333
10 '1/30 .0333333

F8 would contain the formula I wrote, and you sort the entire table by
column F.

Hope that helps and if it does please vote for this answer.

G.Morales

"Split Window Diner" wrote:

> I need to sort products by the horsepower. Horsepower can be a fraction,
> decimal or whole number. For example, I need 1/100 to come before 1/10,
> 1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before
> 1...etc.

"Mexage" wrote:

> Why don't you use the Fraction format under Number format?
>
> That way you only enter the decimal value and it will be represented by it's
> fraction, but the sorter won't be confused.
>
> Another option is to use this formula:
>
> =IF(ISERR(SEARCH("/",E8)),VALUE(E8),LEFT(E8,SEARCH("/",E8)-1)/RIGHT(E8,SEARCH("/",E8)-1))
>
> Where E8 is the value you need to convert from fraction or decimal to
> decimal. With this formula you can create another column next to the
> fractions, and you can sort by it's decimal values...
>
> E F
> 8 '1/2 .5
> 9 '1/3 .33333
> 10 '1/30 .0333333
>
> F8 would contain the formula I wrote, and you sort the entire table by
> column F.
>
> Hope that helps and if it does please vote for this answer.
>
> G.Morales
>
> "Split Window Diner" wrote:
>
> > I need to sort products by the horsepower. Horsepower can be a fraction,
> > decimal or whole number. For example, I need 1/100 to come before 1/10,
> > 1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before
> > 1...etc.
This did not work on all of the values in the column. Some fractions did
not convert to decimal correctly and some did.

What were the fractions that didn't convert?
And what did the formula return for those?


Splt Window Diner wrote:
>
> "Mexage" wrote:
>
> > Why don't you use the Fraction format under Number format?
> >
> > That way you only enter the decimal value and it will be represented by it's
> > fraction, but the sorter won't be confused.
> >
> > Another option is to use this formula:
> >
> > =IF(ISERR(SEARCH("/",E8)),VALUE(E8),LEFT(E8,SEARCH("/",E8)-1)/RIGHT(E8,SEARCH("/",E8)-1))
> >
> > Where E8 is the value you need to convert from fraction or decimal to
> > decimal. With this formula you can create another column next to the
> > fractions, and you can sort by it's decimal values...
> >
> > E F
> > 8 '1/2 .5
> > 9 '1/3 .33333
> > 10 '1/30 .0333333
> >
> > F8 would contain the formula I wrote, and you sort the entire table by
> > column F.
> >
> > Hope that helps and if it does please vote for this answer.
> >
> > G.Morales
> >
> > "Split Window Diner" wrote:
> >
> > > I need to sort products by the horsepower. Horsepower can be a fraction,
> > > decimal or whole number. For example, I need 1/100 to come before 1/10,
> > > 1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before
> > > 1...etc.
> This did not work on all of the values in the column. Some fractions did
> not convert to decimal correctly and some did.

--

Dave Peterson

Perhaps values like 1 1/2
in which case you would be better off using a macro.

Ok... here it goes without macros:

=IF(ISERR(SEARCH("/",A1)),VALUE(A1),IF(ISERR(SEARCH("
",TRIM(A1))),LEFT(A1,SEARCH("/",A1)-1)/MID(A1,SEARCH("/",A1)+1,10),VALUE(LEFT(A1,SEARCH("
",TRIM(A1))))+MID(A1,SEARCH(" ",TRIM(A1)),SEARCH("/",A1)-SEARCH("
",TRIM(A1)))/MID(A1,SEARCH("/",A1)+1,10)))

I know it's ugly, but it will take in numbers such as '1 1/3
Please only use one space between the fraction and the integer.

Hope that helps
if it does, please vote for this post

G.Morales
"Dave Peterson" wrote:

> What were the fractions that didn't convert?
> And what did the formula return for those?
>
>
> Splt Window Diner wrote:
> >
> > "Mexage" wrote:
> >
> > > Why don't you use the Fraction format under Number format?
> > >
> > > That way you only enter the decimal value and it will be represented by it's
> > > fraction, but the sorter won't be confused.
> > >
> > > Another option is to use this formula:
> > >
> > > =IF(ISERR(SEARCH("/",E8)),VALUE(E8),LEFT(E8,SEARCH("/",E8)-1)/RIGHT(E8,SEARCH("/",E8)-1))
> > >
> > > Where E8 is the value you need to convert from fraction or decimal to
> > > decimal. With this formula you can create another column next to the
> > > fractions, and you can sort by it's decimal values...
> > >
> > > E F
> > > 8 '1/2 .5
> > > 9 '1/3 .33333
> > > 10 '1/30 .0333333
> > >
> > > F8 would contain the formula I wrote, and you sort the entire table by
> > > column F.
> > >
> > > Hope that helps and if it does please vote for this answer.
> > >
> > > G.Morales
> > >
> > > "Split Window Diner" wrote:
> > >
> > > > I need to sort products by the horsepower. Horsepower can be a fraction,
> > > > decimal or whole number. For example, I need 1/100 to come before 1/10,
> > > > 1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before
> > > > 1...etc.
> > This did not work on all of the values in the column. Some fractions did
> > not convert to decimal correctly and some did.
>
> --
>
> Dave Peterson
>

Hi!

I get the feeling your "numbers" for Horsepower are entered as text.

If they were true numbers, they would respond to sorting.

You can usually convert text to number (use a helper column if you like) by either adding zero to them all or multiplying them all by 1. To do this, use copy and then paste special using operation add or multiply.

Alf

Originally Posted by Mexage

Perhaps values like 1 1/2
in which case you would be better off using a macro.

Ok... here it goes without macros:

=IF(ISERR(SEARCH("/",A1)),VALUE(A1),IF(ISERR(SEARCH("
",TRIM(A1))),LEFT(A1,SEARCH("/",A1)-1)/MID(A1,SEARCH("/",A1)+1,10),VALUE(LEFT(A1,SEARCH("
",TRIM(A1))))+MID(A1,SEARCH(" ",TRIM(A1)),SEARCH("/",A1)-SEARCH("
",TRIM(A1)))/MID(A1,SEARCH("/",A1)+1,10)))

I know it's ugly, but it will take in numbers such as '1 1/3
Please only use one space between the fraction and the integer.

Hope that helps
if it does, please vote for this post

G.Morales
[/color]

Worked grand that man, just the jobbie - thanks a lot you're a life saver!

-Tom