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

S

Split Window Diner

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.
 
M

Mexage

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
 
S

Splt Window Diner

Mexage said:
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
Click to expand...
This did not work on all of the values in the column. Some fractions did
not convert to decimal correctly and some did.
 
D

Dave Peterson

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

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
 
Top