- Thread starter Gill
- Start date

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

The result should leave 2 in one column and 7 in the next you can then do a simple calculation.

Hope this helps

Andy

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!

Had coffee - answer is - assuming you had 2/7 as text in say cell A5 :-

=MID(A5,1,1)/MID(A5,3,1)

Andy

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

Cheers

Alan

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.Waterhead37":3jnnz7vz said:

Had coffee - answer is - assuming you had 2/7 as text in say cell A5 :-

=MID(A5,1,1)/MID(A5,3,1)

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

Alan, you beat me to it.

Andrew

Andrew

Alan, Andrew,

You are dead right, I should have had two cups of coffee..

You are dead right, I should have had two cups of coffee..

not it you format the complete column or cells to be fractions.if I type 2/7 into a cell it interprets it as 02-Jul.

then you can have 400/546 if you wanted

Andy

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.LyNx":2vjhqwk6 said:not it you format the complete column or cells to be fractions.

then you can have 400/546 if you wanted

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.

A

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

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 .

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 .

Now after my first coffee of the day and a bit of further reading...LyNx":1ouccg5u said:how would the equations above handle 3 3/16 into decimal.

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

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.LyNx":i1gf62yz said:Andrew. Thats how i noted it at the start

Andrew

Works fine, Andy - just leave a space between the whole number and the fractionLyNx":38ehaawr said:I was just wondering how it would work out for the long formula's if a whole number was added. :?

Cheers,

Neil