thestakingmachine.com

TSM - Forum

TSM Forum Home     Download TSM     Purchase TSM     Support     TSMapp     Back to Main TSM Site 

It is currently Thu Aug 28, 2014 4:06 pm
FRSapp Banner


All times are UTC [ DST ]




Post new topic Reply to topic  [ 10 posts ] 
Author Message
PostPosted: Mon Jun 25, 2007 6:38 pm 
Offline

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
 Profile  
 
 Post subject:
PostPosted: Wed Jun 27, 2007 12:24 am 
Offline

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
 Profile  
 
 Post subject: auto convert
PostPosted: Wed Jun 27, 2007 7:19 am 
Offline
Site Admin
User avatar

Joined: Wed Jun 20, 2007 4:16 pm
Posts: 588
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
 Profile  
 
PostPosted: Wed Jun 27, 2007 9:06 am 
Offline

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
 Profile  
 
 Post subject: Re: auto convert
PostPosted: Wed Jun 27, 2007 10:43 am 
Offline

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 :lol:


Top
 Profile  
 
PostPosted: Wed Jun 27, 2007 10:59 am 
Offline

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
 Profile  
 
PostPosted: Wed Jun 27, 2007 12:39 pm 
Offline

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

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

Should work now

Phil


Top
 Profile  
 
PostPosted: Wed Jun 27, 2007 1:40 pm 
Offline

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

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

Should work now

Phil

Thank you Phil that does indeed work now.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jul 23, 2007 9:11 am 
Offline

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
 Profile  
 
 Post subject:
PostPosted: Tue Jul 31, 2007 9:53 pm 
Offline

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
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 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 forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
POWERED_BY