Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > General Topics
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 12th July 2006, 07:35 PM
Twodogs Twodogs is offline
Member
 
Join Date: Jan 1970
Posts: 221
Question Excel help again!!!

Good Evening

Wondering what is possible? I have a column with winning dist data as follows
1(1300) 1(2050) 1(2500) 1(3050) 1(3200)

What I would like to be able to do is have a formula which would give the winning distance range like this 1300-3200 in an alternative column.

Thanks
Twodogs
Reply With Quote
  #2  
Old 13th July 2006, 05:09 AM
jfc jfc is offline
Member
 
Join Date: Jan 1970
Location: Sydney
Posts: 402
Default

Quote:
Originally Posted by Twodogs
Good Evening

Wondering what is possible? I have a column with winning dist data as follows
1(1300) 1(2050) 1(2500) 1(3050) 1(3200)

What I would like to be able to do is have a formula which would give the winning distance range like this 1300-3200 in an alternative column.

Thanks
Twodogs


=CONCATENATE(SUBSTITUTE(MID(A1,1+FIND("(",A1),4),")","")," - ",SUBSTITUTE(LEFT(RIGHT(A1,5),4),"(",""))
Reply With Quote
  #3  
Old 13th July 2006, 06:12 AM
Twodogs Twodogs is offline
Member
 
Join Date: Jan 1970
Posts: 221
Thumbs up

Thanks JFC

Works a treat!!! Don't for a moment understand it but all I can say is you beauty!!

Twodogs

ps what would be needed when there is no data in the column due to no winning distances to return a a zero rather than "value"

Last edited by Twodogs : 13th July 2006 at 06:20 AM.
Reply With Quote
  #4  
Old 13th July 2006, 09:18 AM
Twodogs Twodogs is offline
Member
 
Join Date: Jan 1970
Posts: 221
Default

Hi JFC

Can I overcome the fact that when the column with winning distances is empty due to no winning distance I get a "value" error or would I need to look at the column with the winning distances and do something with that data first before applying your formula.

Thanks
Twodogs
Reply With Quote
  #5  
Old 13th July 2006, 09:40 AM
jfc jfc is offline
Member
 
Join Date: Jan 1970
Location: Sydney
Posts: 402
Default

Quote:
Originally Posted by Twodogs
Hi JFC

Can I overcome the fact that when the column with winning distances is empty due to no winning distance I get a "value" error or would I need to look at the column with the winning distances and do something with that data first before applying your formula.

Thanks
Twodogs


=IF(LEN(A1)>3,CONCATENATE(SUBSTITUTE(MID(A1,1+FIND("(",A1),4),")","")," - ",SUBSTITUTE(LEFT(RIGHT(A1,5),4),"(",""))," 0 ")
Reply With Quote
  #6  
Old 13th July 2006, 11:38 AM
Twodogs Twodogs is offline
Member
 
Join Date: Jan 1970
Posts: 221
Talking

Thanks JFC,

Your time and help is much appreciated.

Twodogs
Reply With Quote
  #7  
Old 7th August 2006, 02:58 PM
Twodogs Twodogs is offline
Member
 
Join Date: Jan 1970
Posts: 221
Question

Good Afternoon

Can anyone help with following? I have searched for an answer but it is beyond my knowledge unfortunately.

I have this data in one column which is the winning distances and the number of times they have won at that distance 1(1000) 1(1200) 1(1300) 1(1450) 1(1550)

In a second column I have the distance of todays race 1000

In a third column I would like have a yes or no for the following. The horses winning distance range from the above data is 1000-1550 and it is contesting a 1000m race so that would be a yes as it fits within the the horses winning distance range. Of course if todays race was 1800m it would be a no.

I do have a column that JFC helped me with which could be used if it is easier which just has the winning distance range.



Thanks
Twodogs
Reply With Quote
  #8  
Old 7th August 2006, 03:52 PM
jfc jfc is offline
Member
 
Join Date: Jan 1970
Location: Sydney
Posts: 402
Default

Quote:
Originally Posted by Twodogs
Good Afternoon

Can anyone help with following? I have searched for an answer but it is beyond my knowledge unfortunately.

I have this data in one column which is the winning distances and the number of times they have won at that distance 1(1000) 1(1200) 1(1300) 1(1450) 1(1550)

In a second column I have the distance of todays race 1000

In a third column I would like have a yes or no for the following. The horses winning distance range from the above data is 1000-1550 and it is contesting a 1000m race so that would be a yes as it fits within the the horses winning distance range. Of course if todays race was 1800m it would be a no.

I do have a column that JFC helped me with which could be used if it is easier which just has the winning distance range.



Thanks
Twodogs



TwoDogs,

Originally you asked for something to produce a column like :

1300-3200

But that is not very useful.

Instead you probably need to get the 1300 alone in one column and 3200 in the next.

So try and do that by pulling apart that long-winded answer I gave you.

Then if

B = today's distance
C = minimum winning distance
D = maximum winning distance

Enter this into E

=IF(AND(B1>=C1,B1<=D1),"yes","no")
Reply With Quote
  #9  
Old 7th August 2006, 04:09 PM
Twodogs Twodogs is offline
Member
 
Join Date: Jan 1970
Posts: 221
Default

Thanks JFC

I will give it a shot and see how I go although I must admit to not fully understanding the original formula you gave me. Works a treat all the same!!!

Twodogs

Last edited by Twodogs : 7th August 2006 at 04:22 PM.
Reply With Quote
  #10  
Old 7th August 2006, 05:58 PM
Twodogs Twodogs is offline
Member
 
Join Date: Jan 1970
Posts: 221
Default

Hi JFC

Worked it out. For the min dist

=IF(LEN(A2)>3,CONCATENATE(SUBSTITUTE(MID(A2,1+FIND("(",A2),4),")","")," "),"0")

and max dist

=IF(LEN(A2)>3,CONCATENATE(SUBSTITUTE(LEFT(RIGHT(A2,5),4),"(","")),"0")

Now when I apply =IF(AND(B1>=C1,B1<=D1),"yes","no") I get no for all results but if I remove the formulas ( copy and paste special values only) I get yes and no where applicable. JFC what do I need to do so I don't have to remove the formulas from the min and max dist columns?

Twodogs
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump



All times are GMT +10. The time now is 10:03 PM.


Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
©2008 OZmium Pty. Ltd. All rights reserved . ACN 091184655