#nav { width: 100%; float: left; margin: 0 0 3em 0; padding: 0; list-style: none; border-bottom: 1px solid #ccc; border-top: 1px solid #ccc; } #nav li { float: left; } #nav li a { display: block; padding: 8px 15px; text-decoration: none; font-weight: bold; color: #069; border-right: 1px solid #ccc; } #nav li a:hover { color: #c00; background-color: #fff; } /* End navigation bar styling. */
 It is currently Sun May 24, 2015 9:59 pm

 All times are UTC [ DST ]

 Page 1 of 1 [ 10 posts ]
 Print view Previous topic | Next topic
Author Message
 Post subject: Auto Convert Fractional to Decimal in Excel?Posted: Mon Jun 25, 2007 6:38 pm

Joined: Wed Jun 20, 2007 8:54 pm
Posts: 63
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

Top

 Post subject: Posted: Wed Jun 27, 2007 12:24 am

Joined: Wed Jun 20, 2007 8:54 pm
Posts: 63
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].

Top

 Post subject: auto convertPosted: Wed Jun 27, 2007 7:19 am

Joined: Wed Jun 20, 2007 4:16 pm
Posts: 609
Location: London
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 !

Top

 Post subject: Re: Auto Convert Fractional to Decimal in Excel?Posted: Wed Jun 27, 2007 9:06 am

Joined: Sun Jun 24, 2007 8:08 pm
Posts: 21
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

Top

 Post subject: Re: auto convertPosted: Wed Jun 27, 2007 10:43 am

Joined: Wed Jun 20, 2007 8:54 pm
Posts: 63
support wrote:
You learn something new every day dont you. I never knew about
Data --> Text to Columns !

Neither did I until yesterday

Top

 Post subject: Re: Auto Convert Fractional to Decimal in Excel?Posted: Wed Jun 27, 2007 10:59 am

Joined: Wed Jun 20, 2007 8:54 pm
Posts: 63
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...

Top

 Post subject: Re: Auto Convert Fractional to Decimal in Excel?Posted: Wed Jun 27, 2007 12:39 pm

Joined: Sun Jun 24, 2007 8:08 pm
Posts: 21
Sorry

Should work now

Phil

Top

 Post subject: Re: Auto Convert Fractional to Decimal in Excel?Posted: Wed Jun 27, 2007 1:40 pm

Joined: Wed Jun 20, 2007 8:54 pm
Posts: 63
dibbles wrote:
Sorry

Should work now

Phil

Thank you Phil that does indeed work now.

Top

 Post subject: Posted: Mon Jul 23, 2007 9:11 am

Joined: Wed Jun 27, 2007 7:52 pm
Posts: 3
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

Top

 Post subject: Posted: Tue Jul 31, 2007 9:53 pm

Joined: Tue Jul 31, 2007 9:38 pm
Posts: 6
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

Top

 Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending
 Page 1 of 1 [ 10 posts ]

 All times are UTC [ DST ]

#### Who is online

Users browsing this forum: No registered users and 1 guest

 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot post attachments in this forum

Search for: