• We invite you to join UKWorkshop.
    Members can turn off viewing Ads!

Converting Fractions In Excel

UKworkshop.co.uk

Help Support UKworkshop.co.uk:

Gill

Established Member
Joined
3 Sep 2003
Messages
3,537
Reaction score
0
Location
Lincs
Are there any software gurus out there who can tell me how to convert fractions (such as "2/7") from text in one cell into decimal in another cell (ie "0.286") using Excel?

Gill
 

LyNx

Established Member
Joined
27 Jul 2005
Messages
1,159
Reaction score
0
Location
swindon
Yes. In the second cell, make this an = of the first cell. (place the equal sign in the second cell then left click into the first cell, ie: cell B1 formula is =A1 )

Then in the first cell "right click" and format cell and change the Category to fraction. So when you add a fraction into the first cell, it copies this into the second cell but converts to decimal numbering

Works in excel 2003

hope this helps

Andy
 

dedee

Established Member
Joined
24 Jul 2003
Messages
2,637
Reaction score
1
Location
14860, France
Gill, do you know how to convert text to columns? In the data menu select text to column, select delimited in step one, in step two select Other and enter a / in the box, in step 3 select general for both columns.
The result should leave 2 in one column and 7 in the next you can then do a simple calculation.

Hope this helps


Andy
 

Chris Knight

Established Member
Joined
14 Jan 2004
Messages
6,641
Reaction score
3
Location
SE London - NW Kent
Gill,
You need to use the MID function to extract the relevant text, thus in your example if you enter the following in a cell =MID(cellref,1,1) where my term cellref refers to where your fraction 2/7 is, then in the cell where you placed the formula, you get 2 returned. If you likewise made the function =MID(cellref,3,1) you get a 7 returned.

Thus if you placed these functions in two cells (say they were A1 and A2 and then in the cell where you wanted the decimal answer, you simply placed the formula A1/A2 you will get the answer - 0.285714

You could combine all this in a single formula but I have separated it to keep it as clear as possible - and because I need another cup of coffee before doing it!
 

LyNx

Established Member
Joined
27 Jul 2005
Messages
1,159
Reaction score
0
Location
swindon
Gill, if you add =(25.4*A1) for the formula in the second columns then this will convert from fractions (first column) to mm in second. If that is what your after.

Simple solution :wink:

Andy
 

dedee

Established Member
Joined
24 Jul 2003
Messages
2,637
Reaction score
1
Location
14860, France
Well that just goes to show that there is more than one way to skin a cat or convert fractions in excel :lol:

Andy
 

sxlalan

Established Member
Joined
21 Apr 2005
Messages
242
Reaction score
0
Location
Skipton, Yorkshire
This is slightly more generic as it looks for the / rather than assuming it is a certain number of characters in

=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,LEN(A1))

Cheers

Alan
 

andrewm

Established Member
Joined
30 Oct 2003
Messages
616
Reaction score
0
Location
Cambridge, UK
Waterhead37":3jnnz7vz said:
GILL,
Had coffee - answer is - assuming you had 2/7 as text in say cell A5 :-

=MID(A5,1,1)/MID(A5,3,1)
But that only works if the fraction has a single digit on top and on the bottom. It will fall down for something like 15/16.

A more generic solution (again assuming that the value is in cell A5) would be:

=LEFT(A5,FIND("/",A5)-1)/RIGHT(A5,LEN(A5)-FIND("/",A5))

Gill, how are your getting your fractions into the cell in the first place? if I type 2/7 into a cell it interprets it as 02-Jul. I need to preceed it with a single quote to get it as text. I am sure that if we knew what you were trying to do there might be a more elegent solution.

Andrew
 

LyNx

Established Member
Joined
27 Jul 2005
Messages
1,159
Reaction score
0
Location
swindon
if I type 2/7 into a cell it interprets it as 02-Jul.
not it you format the complete column or cells to be fractions.

then you can have 400/546 if you wanted

Andy
 

andrewm

Established Member
Joined
30 Oct 2003
Messages
616
Reaction score
0
Location
Cambridge, UK
LyNx":2vjhqwk6 said:
not it you format the complete column or cells to be fractions.

then you can have 400/546 if you wanted
Too true. I was starting with a new sheet and didn't know what Gill was doing. Of course, Excel being ever helpful will keep 400/456 as a fraction even if it is not formatted as text, it's only if it thinks you are trying to enter a date that it will convert it. I do think that some of these programs can be too clever for their own good.

Andrew - who has spent many a day trying to get Excel (or Word) to do what he wants and not what Mr Gates thinks it should be.
 

LyNx

Established Member
Joined
27 Jul 2005
Messages
1,159
Reaction score
0
Location
swindon
how would the equations above handle 3 3/16 into decimal.

I'm on the understanding that Gill wants to convert inches to MM??

Andy
 
A

Anonymous

Guest
Having read through all the responses I am left wondering if Gill is confused yet? :)
 

Gill

Established Member
Joined
3 Sep 2003
Messages
3,537
Reaction score
0
Location
Lincs
Dare I level with you guys :? ? I don't need a morality lecture.

As I'm sure some of you might be aware, there are various bookmakers on the internet who quote odds in fraction format, eg 2/7, 100/30 and so on. I want to be able to cut and paste these into a spreadsheet for further analysis; the cutting and pasting is easy but it isn't possible to work with fractions in a spreadsheet, hence the need to convert them into decimals.

I used to do this sort of stuff a few years ago and I was surprised to find that certain procedures could eliminate the bookmakers' edge. Unfortunately, I never managed to generate an edge for the punter :(. Still, it was fun messing about with the data and it didn't cost me any money*.

All that was quite a while ago and the spreadsheets I wrote seem to have vanished from my computers. I can't remember what I did before, it took me quite a while to learn how to use Excel because programming doesn't come easily to me. I do recall that the most difficult part of the procedure was converting the fractions into decimals. Seeing the post about splitting text into separate columns is ringing very loud bells and I believe that was how I did it in the past, using a macro. Nevertheless, I like the simplicity of being able to use formulae in cells so I'll also have a look at the other methods that have been suggested.

Many thanks for all the help, guys :D .

Gill

*Actually, I'm not a gambler - the last time I had a bet was in 1985 when I dropped a jackpot from a fruit machine and I resolved to quit while I was ahead :).
 

andrewm

Established Member
Joined
30 Oct 2003
Messages
616
Reaction score
0
Location
Cambridge, UK
LyNx":1ouccg5u said:
how would the equations above handle 3 3/16 into decimal.
Now after my first coffee of the day and a bit of further reading...

If we a talking about having to format a cell in order to enter a fraction then why not just format the cell as a fraction. Select fraction as the cell format and select up to two (or up to three) digits. then Gill can just use the value in the cell in her calculations. It will then work for things like 3 3/16.

Andrew
 

LyNx

Established Member
Joined
27 Jul 2005
Messages
1,159
Reaction score
0
Location
swindon
Andrew. Thats how i noted it at the start :roll:

I was just wondering how it would work out for the long formula's if a whole number was added. :?

Andy
 

andrewm

Established Member
Joined
30 Oct 2003
Messages
616
Reaction score
0
Location
Cambridge, UK
LyNx":i1gf62yz said:
Andrew. Thats how i noted it at the start
Sorry Andy, should have read your post a bit better. For some reason I thought you were talking about formatting a cell as Text which is what I had been doing to avoid conversion to a date. Looks like you were already one step ahead of me.

Andrew
 

Neil

Established Member
Joined
7 Oct 2003
Messages
1,016
Reaction score
0
Location
Ireland
LyNx":38ehaawr said:
I was just wondering how it would work out for the long formula's if a whole number was added. :?
Works fine, Andy - just leave a space between the whole number and the fraction :)

Cheers,
Neil
 

Latest posts

Top