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 28th September 2010, 12:55 PM
Pauls123 Pauls123 is offline
Member
 
Join Date: Oct 2005
Posts: 245
Default Calling all Excel Experts please,...!

Hi there,

I'm looking for a bit of help on "excel" please. Let's say I have rated every horse in every race at sydney and melbourne following each meeting on a saturday. I've given them all a numerical value which I have determined as a kg value following its run.

All of the horses go into column A and all the ratings go into column B, next to each horse. As you can imagine my columns will start to get very lengthy with hundreds of horses in them. Then next saturday comes along, say Race 1 at sydney and I want to apply my rating for each horse in the race (I wont have all of them, due to horses resuming, coming off provincials etc).

Can I say copy and paste the fields into this spreadsheet, it then finds each horse and allots its rating next to it, or is this asking to much,...!

I know I can do a "find" but that would mean doing each horse individually, is there any faster way.

Thanks and regards
Paul
Reply With Quote
  #2  
Old 28th September 2010, 02:24 PM
stugots stugots is offline
Banned
 
Join Date: Jan 1970
Posts: 879
Default

see how you go with the attached Paul

uses vlookup
Reply With Quote
  #3  
Old 28th September 2010, 02:28 PM
stugots stugots is offline
Banned
 
Join Date: Jan 1970
Posts: 879
Default

file might help
Attached Files
File Type: xls nags_ratings.xls (18.5 KB, 709 views)
Reply With Quote
  #4  
Old 28th September 2010, 06:46 PM
Pauls123 Pauls123 is offline
Member
 
Join Date: Oct 2005
Posts: 245
Default

thanks Stugots, I am playing around with your formula now. It works excellent on a simple calculation, but when I apply it to my spreadsheet I am getting some number (rating), that is not correct. When I do a fill down, that same incorrect number keeps coming up.

I'm still trying here,

Thanks again, Paul
Reply With Quote
  #5  
Old 28th September 2010, 07:18 PM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default

You need to make sure your Database of horses are listed in alphabetical order.
Reply With Quote
  #6  
Old 28th September 2010, 08:03 PM
Pauls123 Pauls123 is offline
Member
 
Join Date: Oct 2005
Posts: 245
Default

thanks Moeee, Yes I've done a a-z sort of all the horses, but something is still not working right. Still getting some incorrect number (rating), when its run over a large number of horses. But in a simple example like Stugots sent me, it is working fine.

Thanks again, Paul
Reply With Quote
  #7  
Old 28th September 2010, 08:21 PM
moeee moeee is offline
Suspended
 
Join Date: Jan 1970
Location: Melbourne
Posts: 5,359
Default

Try this Formula instead
Substitute the NEW for the OLD in the supplied Spreadsheet.

OLD =LOOKUP(F3,A$3:A$7,B$3:B$7)

NEW =VLOOKUP(F3,A$3:B$7,2)

And again, your database of animals must be in alphabetical order.

EDIT : The above Formula goes into cell G3 in the spreadsheet.
Copy this cell all the way down to G7

Last edited by moeee : 28th September 2010 at 08:24 PM. Reason: added something
Reply With Quote
  #8  
Old 28th September 2010, 08:41 PM
Pauls123 Pauls123 is offline
Member
 
Join Date: Oct 2005
Posts: 245
Default

Hi Moeee, thanks again. I just tried that and still getting some number that is totally wrong. Once again it works fine in a simple small spreadsheet. I then thought that maybe it was due to some of my horses having the (NZ) after their name, so went through and deleted all those (NZ)'s.

Still this same incorrect number.

Thanks again, Paul
Reply With Quote
  #9  
Old 28th September 2010, 09:25 PM
Pauls123 Pauls123 is offline
Member
 
Join Date: Oct 2005
Posts: 245
Default

Hi again Moeee. When I retype about half a dozen of my horses and retype the ratings into another spreadsheet, this works fine, both yours and Stugots.

So I am thinking it must be the format of the names and numbers that I have copied and pasted from the site where I got these from originally. This being the TVN sectional time site. But as far as I can see the cells are just formatted as either general or numbers.

Must be a solution that I am missing,

Paul
Reply With Quote
  #10  
Old 28th September 2010, 09:39 PM
ixlat0 ixlat0 is offline
Member
 
Join Date: Feb 2009
Posts: 45
Default

paul123

you were correct to clean up the data to "only" include the horse name -- even a space bar input (which you can't see) will marr the output and you will see #N/A

i suggest sheet2 become your data base

copy all horse names in sheet2 column A
copy all ratings in sheet2 column B

in sheet1 paste the horse name in column A (leave a gap between races for clarity)

copy this formula in sheet1 columm B as far down as appropriate

=IF(A1="","",VLOOKUP(A1,Sheet2!A$1:B$1000,2,FALSE))

comment:
the VLOOKUP command as used here isn't case sensitive so don't worry abt that

you need only clean up column A and start again for a new meeting and it keeps your data safe

A$1:B$1000 = the size of you data base (eg 1000 horses) if there are less it doesn't matter it will grow

alphabetical order shouldn't be an issue but probably needed to keep your data base up-to-date

hope this helps

good luck!
__________________
never smile at a crocodile -- don't be taken in by his welcome grin

Last edited by ixlat0 : 28th September 2010 at 09:54 PM.
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 03:10 PM.


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