Auto Convert Fractional to Decimal in Excel?

Ask for help here and help each other out !
DeepJoy
Posts: 63
Joined: Wed Jun 20, 2007 8:54 pm

Auto Convert Fractional to Decimal in Excel?

Postby DeepJoy » Mon Jun 25, 2007 6:38 pm

Yep, I know how to do it 'manually': divide the fraction 'by itself' and add 1, e.g., 9/2 is 4.50 +1 = 5.50, but I need to convert over 3,000 of them in an Excel file and I'm only a basic Excel user, having no training, just experience.

The Excel file is in this format:<PRE>
A B C D E F G
Date Track Time Name Rating W or L SP
</PRE>
...so the question is how do I convert the contents of column G to decimal odds? I have a basic understanding of Excel and can follow instructions - can anyone help?

Thanks

Paul
DeepJoy
Posts: 63
Joined: Wed Jun 20, 2007 8:54 pm

Postby DeepJoy » Wed Jun 27, 2007 12:24 am

I think there must be a better way to do it, but I eventually hit on this somewhat long-winded approach:-

I split the fractional odds into two columns, and then used a formula in a third column to "divide the fraction 'by itself', and then add one to the result". The fractional odd needed to be in Text format.

11/4 was in Column A, so by using Data -> Text To Column, the 11 and the 4 were split into columns A and B, and then the following formula was used in Column C:

=(A1/B1)+1

...which created 3.75 in column C.

[Technically =($A1/$B1)+1 was used so the 'relative reference' formula could be copied to the rest of the C column].
User avatar
support
Site Admin
Posts: 614
Joined: Wed Jun 20, 2007 4:16 pm
Location: London

auto convert

Postby support » Wed Jun 27, 2007 7:19 am

DeepJoy
There is an easier way. I just can't remember what it is at the moment !
I had something working once when i was copying some data with fractions from a web page into excel. I'll try and dig it up.
You learn something new every day dont you. I never knew about
Data --> Text to Columns !
dibbles
Posts: 21
Joined: Sun Jun 24, 2007 8:08 pm

Re: Auto Convert Fractional to Decimal in Excel?

Postby dibbles » Wed Jun 27, 2007 9:06 am

Hi Guys/Gals



You could try

Assuming your fraction is in column G
In column H you put "=DOLLAR(G7)" in one column
and in column i you put "=REPLACE(F7,1,1,"")+1" to get rid of the £ sign and add one to the odds

Hope this helps

Phil
DeepJoy
Posts: 63
Joined: Wed Jun 20, 2007 8:54 pm

Re: auto convert

Postby DeepJoy » Wed Jun 27, 2007 10:43 am

support wrote:You learn something new every day dont you. I never knew about
Data --> Text to Columns !

Neither did I until yesterday :lol:
DeepJoy
Posts: 63
Joined: Wed Jun 20, 2007 8:54 pm

Re: Auto Convert Fractional to Decimal in Excel?

Postby DeepJoy » Wed Jun 27, 2007 10:59 am

dibbles wrote:Hi Guys/Gals

You could try

Assuming your fraction is in column G
In column H you put "=DOLLAR(G7)" in one column
and in column i you put "=REPLACE(F7,1,1,"")+1" to get rid of the £ sign and add one to the odds

Hope this helps

Phil

Much appreciate the suggestion Phil, but if I do that, I end up with column I saying #VALUE!

I suspect my lack of structured training is showing here and the solution is easy... :?
dibbles
Posts: 21
Joined: Sun Jun 24, 2007 8:08 pm

Re: Auto Convert Fractional to Decimal in Excel?

Postby dibbles » Wed Jun 27, 2007 12:39 pm

Sorry

Should read "=REPLACE(H7,1,1,"")+1"

Should work now

Phil
DeepJoy
Posts: 63
Joined: Wed Jun 20, 2007 8:54 pm

Re: Auto Convert Fractional to Decimal in Excel?

Postby DeepJoy » Wed Jun 27, 2007 1:40 pm

dibbles wrote:Sorry

Should read "=REPLACE(H7,1,1,"")+1"

Should work now

Phil

Thank you Phil that does indeed work now.
dave34
Posts: 3
Joined: Wed Jun 27, 2007 7:52 pm

Postby dave34 » Mon Jul 23, 2007 9:11 am

I am maybe missing something here but you cannot use either of the two suggestions above unless the data in cloumn F is stored as a number already and then you would just add 1 to convert it to decimal odds =f7+1 in the adjacent column. To convert text to numbers go to Tools/Options/Error checking or better still type "convert text to numbers" in the help menu. Must confess I have ghad limited success with this particularly if copying from a webpage

Regards
Dave
PaulB
Posts: 6
Joined: Tue Jul 31, 2007 9:38 pm

Postby PaulB » Tue Jul 31, 2007 9:53 pm

Below is how I would do it. I'm assuming that you have a text cell G1 with a fractional odds value in it:

=(LEFT(G1,FIND("/",G1)-1)/MID(G1,FIND("/",1)+1,4))+1

So 9/2 becomes 9 and 2, then 9 is divided by 2, and finally 1 is added. Hope that helps. Sorry for the late reply, I only joined the forum today.

Paul

Return to “Ask For Help ”

Who is online

Users browsing this forum: No registered users and 1 guest