converting fractions less than 1

[Liz]

I am converting a column of fractions to decimal. When I do this either by
format - cells - number - decimal, or by using a FIXED formula (i.e:
=FIXED(I25,4,TRUE)) it will convert properly IF the original fraction is
greater or equal to one. I have 1/2 in a cell, it will not convert to a
decimal. How the cell is formatted (either text or number) does not make a
difference with the conversion. Here is what happens: If the cell that has
1/2 as the value is formatted as text or as a number, and I then convert it
to a fraction, it gives me a 5 digit number: 37988. 3/4 is translated to
38050, 5/16 is translated into 38115, etc. If I then go and double click in
the cell that reads 1/2, the 5 digit number then turns into a decimal.
Double clicking in the cell that reads 37988 does nothing. There has to be a
simpler way. I just want to convert all fractions to decimals. In summary
my questions are: 1) where are these 5 digit numbers coming from? 2) why are
numbers less than 1 not converting to a regular decimal format? 3) what
formula functions or procedure would I use to streamline this problem without
having to do so much manual manipulation?

Thanks for any advice.
Liz

 

[duane]

if you do not have the cells formatted to be numbers, entering 3/4 wil
be interpreted as March 4, so first format the range as a number, an
if you then enter 3/4 it will be 0.7

 

[Tom Ogilvy]

the five digit number is how Excel stores Dates. It is interpreting 1/2 as
a date

37988 is 2 January 2004, so it uses the current year, interprets the 1 as
January and 2 as the day.

 

[Myrna Larson]

There's no need to "convert" a fraction to a decimal. It's already stored as a
decimal if Excel has recognized it as a number. Just format it differently,
i.e. as number with 2 decimal places rather than with a fraction format.

As for your "problem" numbers, the cell doesn't contain the number 0.75.

Excel has already translated 3/4 to a date, March 4, 2004. Ditto with 5/6,
1/2, etc.

I would remove the formatting for these cells so you can see what the actual
content is. For the numbers that are being interpreted as dates, for recent
dates, you should see values in the 37,000-38,000 range.

Assuming your real fractions all have values <= 366, you could put this
formula in another column and copy down.

=IF(YEAR(A1)<1901,A1,MONTH(A1)/DAY(A1))

Now Edit/Copy the column of formulas, then Edit/Paste Special and select the
Values option to convert the formulas to their values, and apply whatever
format you want. Then you can delete the column containing the original
values.

 

[Dana DeLouis]

Just to add. If you want to enter a fraction less than 1 (ie 1/2), you need
to have a leading zero, followed by a space.
ie 0 1/2.

This will enter it correctly as 0.5.
HTH