Need to consolidate large quantities of data and vlookup errors out

I've got a large table with horse racing odds that go fron .10-1 t
150-1 in .10 increments with values assigned to those odds, so it look
like this:

A1     B1
.10-1    4
.20-1    4
.30-1    3
etc. all the way up to 150-1.

I tried making a table out of this with 2 columns and my hands wen
numb after inputting about 200 odds entries :)  I tested it out wit
the part I did manage to complete with the vlookup formula and it gav
me some wierd errors for results (maybe because of so many entries)
For example, using the first example above, the user would input .10-
and it would spit out 3 rather than the correct answer of 4.  I kno
using an if-then expression with some >x or <x values would do th
trick, having the user input whole numbers rather than odds but I'
making this spreadsheet for somebody else and would rather they had th
user friendlyness of being able to enter horse odds as they ar
accustomed to seeing in the track program. Any ideas of what type o
formula would be best suited to this large quantity of data and stil
maintain the desired odds input format of 1-1,2-1,3-1 etc. would reall
be appreciated.

Thanks,
Fran

--
bort
-----------------------------------------------------------------------
bortz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2943
View this thread: http://www.excelforum.com/showthread.php?threadid=49223

0
12/9/2005 5:16:49 PM
excel 39879 articles. 2 followers. Follow

3 Replies
262 Views

Similar Articles

[PageSpeed] 0

Did you use exact match in your vlookup?

vlookup(lookup_value,table,index,TRUE/FALSE)

if you leave it or or put 1 or TRUE it will lookup a value but not
necessarily the one you want, if you set it to FALSE it will either return
the exact value or #N/A
However if a user put in 10-1 in the lookup cell without it being formatted
as text it will not find it so make sure it is text, the formula could look
like

=VLOOKUP(A1,C2:D6,2,FALSE)


-- 

Regards,

Peo Sjoblom

"bortz" <bortz.1zs97a_1134148800.6586@excelforum-nospam.com> wrote in
message news:bortz.1zs97a_1134148800.6586@excelforum-nospam.com...
>
> I've got a large table with horse racing odds that go fron .10-1 to
> 150-1 in .10 increments with values assigned to those odds, so it looks
> like this:
>
> A1     B1
> 10-1    4
> 20-1    4
> 30-1    3
> etc. all the way up to 150-1.
>
> I tried making a table out of this with 2 columns and my hands went
> numb after inputting about 200 odds entries :)  I tested it out with
> the part I did manage to complete with the vlookup formula and it gave
> me some wierd errors for results (maybe because of so many entries)?
> For example, using the first example above, the user would input .10-1
> and it would spit out 3 rather than the correct answer of 4.  I know
> using an if-then expression with some >x or <x values would do the
> trick, having the user input whole numbers rather than odds but I'm
> making this spreadsheet for somebody else and would rather they had the
> user friendlyness of being able to enter horse odds as they are
> accustomed to seeing in the track program. Any ideas of what type of
> formula would be best suited to this large quantity of data and still
> maintain the desired odds input format of 1-1,2-1,3-1 etc. would really
> be appreciated.
>
> Thanks,
> Frank
>
>
> -- 
> bortz
> ------------------------------------------------------------------------
> bortz's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=29437
> View this thread: http://www.excelforum.com/showthread.php?threadid=492233
>


0
terre081 (3244)
12/9/2005 5:35:46 PM
Thanks Peo for the tips. I found the error. When I used the handle to
fill down the formula, it was incrementing the cell numbers in the
formula which don't actually correspond to the starting and ending
cells of the vlookup table.

So my formula looked like this:

=VLOOKUP(G4,H27:I122,2,FALSE)

and the H27 and I122 table begin and end identifiers were being
incremented to
h28 and I123 and so on, when I pulled down the formula to the cells
below it. Silly me, expecting something to work as planned. :)

I could sure use a more elegant way of doing the odds calculation
without using vlookup so the user can enter odds in x-x format as
described above, though.

Thanks,
Frank


-- 
bortz
------------------------------------------------------------------------
bortz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29437
View this thread: http://www.excelforum.com/showthread.php?threadid=492233

0
12/10/2005 1:38:22 AM
If you always want to point at H27:i122, then change your formula:

=VLOOKUP(G4,$H$27:$I$122,2,FALSE)

The $ mean that that that portion of the address won't change when you copy the
formula.

Since we used $h and $27 and $i and $122, then that range reference will be
frozen.



bortz wrote:
> 
> Thanks Peo for the tips. I found the error. When I used the handle to
> fill down the formula, it was incrementing the cell numbers in the
> formula which don't actually correspond to the starting and ending
> cells of the vlookup table.
> 
> So my formula looked like this:
> 
> =VLOOKUP(G4,H27:I122,2,FALSE)
> 
> and the H27 and I122 table begin and end identifiers were being
> incremented to
> h28 and I123 and so on, when I pulled down the formula to the cells
> below it. Silly me, expecting something to work as planned. :)
> 
> I could sure use a more elegant way of doing the odds calculation
> without using vlookup so the user can enter odds in x-x format as
> described above, though.
> 
> Thanks,
> Frank
> 
> --
> bortz
> ------------------------------------------------------------------------
> bortz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29437
> View this thread: http://www.excelforum.com/showthread.php?threadid=492233

-- 

Dave Peterson
0
petersod (12005)
12/10/2005 2:34:28 PM
Reply:

Similar Artilces:

Assistance Needed in Comparing 2 MSP versions from differenct sour
I am attempting to compare 2 projects in MSP 2003. I have both open and am using the compare functionality. I am aware that the Unique ID field is a key component and have noted that often these do not match for the same line item from one version to the next as different individuals are preparing this project. When I insert a new line to attain the same Unique ID between both projects for a specific line, and subsequently delete the old line, the compare still does not recognize that these 2 lines are associated and the same. I cannot do this for all lines, but even as a tes...

sorting data in columns
How can I sort multiple columns simultaneously? i.e. I have sixteen columns of numerical data that I want to be able to apply the same Sort to each column individually but I do not want to have to highlight each column separately as this will take considerably longer. Save your workbook first! Try sorting one column, then selecting the next and hit the F4 key. It shouldn't take too long if it works ok. Spiderman wrote: > > How can I sort multiple columns simultaneously? i.e. I have sixteen columns > of numerical data that I want to be able to apply the same Sort to each &...

What to do?
Hi Everybody? I have problem when I try to build offline data file from Pivot tabel which is connected to Cube on the Analysis Server. Excel return this message: No columns that microsoft excel can use were returned from this query! What to do? ...

SMRTINET.INI error
I get this error about every third time I do the internet updates in Money 2004. "Money encountered an error processing the dowloaded file ('SMRTINET.INI')." Yes the error log mispelled the word downloaded! Anybody have any idea? In microsoft.public.money, Stephen wrote: >I get this error about every third time I do the internet >updates in Money 2004. "Money encountered an error >processing the dowloaded file ('SMRTINET.INI')." Yes the >error log mispelled the word downloaded! > >Anybody have any idea? Close Money In Inter...

fatal error LNK1104
I get the following error while trying to link the sqlite.lib Linking... LINK : fatal error LNK1104: cannot open file "sqlib3.lib" Error executing link.exe. Before that, I did the following things: Project->Settings->link tab : Object/library modules: sqlib3.lib Tools->Options->Directories tab and added the folder on where sqlib3.lib resides I've also copy the sqlib3.dll to Window/System32 Why do I get that error? Thanks for ypur time. Ok, i've solved the problem Martin wrote: > I get the following error while trying to link the sqlite.lib > > Li...

Testing for an error in a range of cells...
I searched through the previous posts on this topic, but have almost no experience using VBA in excel, so I was wondering if there was a simpler way to achieve this: I want to write a formula in B2 that will basically say, "If the range of cells B3 to B24 contains an error, return x if true, and return y if false." I know how to do this for a specific cell rather than a range, for example (=IF(ISERROR(B2),"x","y"). Any help in achieving this without delving into VBA would be much appreciated (I just don't have the time to start learning this stuff as I am w...

Compare Data
Let's say I want to compare data thusly: I have two columns of numbers, G and N. Column G has more numbers than column N, and includes every number in column N. In each cell in column O is a string associated with the number in column N. I want to comare a number in column N with all the numbers in column G and when I find a match, copy the string in column O to the appropriate cell in column M. I want to repeat this process until all the strings in column O have been matched to their numbers in column G. Whew! So. How do I do this? Thanks, Tommy One way: M1: =VLOOKU...

Outlook has encountered a fatal error and must close
When composing messages I get the above message. the program closes and I am never sure whether my message has been lost. This message and abrupt shut down only started happening in the last month. Sending the error report has yielded nothing despite sending the error report every second time. Re: Terms of use below: I would just like it fixed!!!! Did you recently install a newer version of Ad-Aware? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft....

Categorizer?
Hi all, i am receiving this situation with Exchage2003 (SBS) Email is downloaded with pop3 connector and then located in internal mailboxes Some mailboxes are not receiving email, other without problems. I do message tracking on these lost messages and i see that right after the message been passed to the categorizer the flow ends up there. I check event viewer and i have error code 1070 talking about not an addresses matching the internal client. i have checked this and the external address is defined in the default recipient policy and has been applied to these internal clients. any cl...

Error message when trying to use my access add-in with VS2008
I created a Access 2007 add-in with VS2008 C# and I'm trying to debug my access form that sends text to a textbox to be displayed after I click on a button. I can step into 'FillTextButton_Click()' in my C# app that gets called when I click on the access form button. So I have my button and textbox connected to Access2007 with the Access onLoad() event. Everything seems to be connected ok. But as soon as I finish running through the 'FillTextButton_Click()' I get a error message saying "The expression On Click u=you entered as the event property se...

CRM 3.0 Installation help...2 errors
Going through the system requirements part of the setup I get a Warning on the IIS part. It cant access the URL-- Error accessing URL http://IRONWOOD-CRM: The remote server returned an error: (500) Internal Server Error. The second error I get is : Setup failed to validate specified Reporting Services Report Server http://ironwood-crm/ReportServer. Error: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. Chris Holub MCP CCNA 1. Check your IIS and default CRM virtual directory is there issue accecing, can you send me detail or s...

Error in saving quote
Hi, when I want to save quote, there is error message like this : <details>Server was unable to process request.</details> Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: <details>Server was unable to process request.</details> Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding t...

Error: "The name could not be resolved. The bookmark is not valid"
I am getting the error as described in the title when I attempt to connect to and my exchange server. 1) Setup One Exchange 2003 organisation (with a back end server in parent domain and one in child domain). Exchange 2003 Forest. A few Exchange 2003 front end servers in DMZ Office 2000 and Office 2003 on Windows 2000 Windows XP clients. ssl OWA and MAPI access (planning to investigate httpsRPC). 2) Problem When I connect with MAPI Outlook clients to child domain Exchange 2003. It comes up with the error: ...

Exchange Calendar update tool = unable to find mailbox timezone:Error 0x80004005
When i run the exchange calendar update tool i rececive the following message "Unable find mailbox timezone:Error 0x80004005". I have given my user account full permissions to each exchange store. What else could be causing this problem? alos how do i grant exchange view only admin, exchange full admin, and send as rights to my user account manullay. This script is not working for me? thanks "Jef A" <jef.aldrich@tatumllc.com> wrote in message news:%23AssN4YYHHA.4008@TK2MSFTNGP05.phx.gbl... > When i run the exchange calendar update tool i rececive the follow...

DATA DUMP WARNING #2
Maybe the ultimate lesson in this is backup backup backup. Do backups early and often. and use a large cycle - don't write a backup onto the immediately previous media! -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback I do not respond to any emails that I have not specifically asked for. <DrWoodardOnDS@hotmail.com> wrote in message news:11095315...

error with exchange 2003
since last week, the application log is full of following message: the service threw an unexpected exception which was caught at f:\titanium\src\lra\abv_dg\serveragent.cpp(4511) this is domain controller (win 2003 server) and exchange server (exchange 2003. the network is running with 8 local computers. i could not find any help related to this error message and i need some help with this issue. thanks Any help from http://groups.google.com/groups?as_epq=service%20threw%20an%20unexpected%20exception%20which%20was%20caught&safe=images&ie=ISO-8859-1&as_ugroup=microsoft.publi...

Rounding errors in manually entered investment account transaction
I am tracking the performance of the investment subaccount of my life insurance policy in MS Money 2006 Premium. My insurer does not support online updates of investment subaccount performance, so I must enter statement data manually. Shares of investments are only reported with 3 decimal places of accuracy on my statements. As a result, in some cases rounding errors in the number of shares purchased and sold accumulate over months so that after a while the total number of shares that I own as reported by Money differs from the number of shares reported by my insurer. How do I adju...

Error reassigning leads following upgrade to version 4
Hi, We are experiencing an issue when trying to reassign a lead to another user. Whenever we do we get the following error : CrmException: More than one user queue was found for the given object.] at Microsoft.Crm.CrmException.Assert(Boolean condition, String message) at Microsoft.Crm.ObjectModel.QueueServiceInternal`1.GetUserQueueWithObject(BusinessEntityMoniker moniker, Guid userId, ExecutionContext context) at Microsoft.Crm.ObjectModel.QueueItemService.RouteToUser(BusinessEntityMoniker objectMoniker, Guid userIdOfCurrentObjectOwner, Guid primaryUserIdOfQueueToRouteTo, Execut...

Error Message 08-17-10
Good Morning, I am trying to make Windows Mail my new mail account. This is the message that keeps popping up. Does anyone have any idea on how I can get connected? Thanks in Advance, Kasey Account: 'Home', Server: 'pop3.zoominternet.net', Protocol: POP3, Server Response: '-ERR authorization first', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Number: 0x800CCC18 yes i am getting same one and just got off of chat with comcast for 1 hr. and still not resolved "Kasey Jennings" wrote: > Good Morning, > I am trying...

Error message with CAPICOM 2.1.0.2 Not enough storage is available
Hi everybody, I need some help. With CAPICOM 2.1.0.1, when I try to store certificates from a smart card I use Store := CoStore.Create; Cert:=CreateComObject(CLASS_Certificate) as ICertificate2; Store.Open(CAPICOM_SMART_CARD_USER_STORE, 'MY',CAPICOM_STORE_OPEN_READ_ONLY); without any problem. I have updated with CAPICOM 2.1.0.2, and the same code produces an error message:"Not enough storage is available to complete this operation" when store.open. I need use this library. My os is Microsoft Windows XP Professional Version 2002. SP3. My Internet e...

error 0X80070057 #3
I cant setup this mail service program. error 0x80070057 -- Thank You It's a long shot but see if the resolution in this article helps: http://support.microsoft.com/default.aspx?scid=kb;en-us;326842 "onelot" <onelot@discussions.microsoft.com> wrote in message news:F031D2BA-12A9-40A9-AFB8-814CF03B967F@microsoft.com... >I cant setup this mail service program. error 0x80070057 ...

excell formula needed
Hi, In Excell, I'm trying to match a cell (eg H21,with a num value (eg 2530) to a column of figures (M34:M99) that has one match in it and produce a grading A, B, C, D from a column (N34:N99). Anyone have any ideas? Rob. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ =Vlookup(H21,$M$34:$N$99,2,false) Bernard "terrificRob" <terrificRob.uixfy@excelforum-nospam.com> wrote in message news:terrificRob.uixfy@excelforum-nospam.com... > Hi, > In Ex...

Need help setting up fields on a liquor store import file.
What is the best way to set up the fields on the excel sheet (import) for a liquor store. Should I put the size of the bottle with the extended description or should i put the size in the sub description? I also have sku's (item lookup codes) for each child item but do not have one for the parent (a case of the product). How should I handle this? Do I make up my own lookup codes or should i just not use the parent child relationship and store everything as a child? What benefits does each one have? I always include the size in the primary description field. I always keep the d...

Getting data from Access to Excel?
How would you go about pulling data from Access database into Excel? (Like weekly reports) Troy, Data|Get External Data|New Database Query Select MS Access Database The Wizard should take you through the remaining steps of selecting the data you want to import and the criteria (if any) that you wish to use. PC "Troy" <corptkm@yahoo.com> wrote in message news:nLDXa.3726$Jk5.3138269@feed2.centurytel.net... > How would you go about pulling data from Access database into Excel? (Like > weekly reports) > > You can also push-it-out of Access to Excel by (While...

macro needs info on currently highlighted message in search folder
I created a search folder, "Messages Sent Or Received Prior To 2008) by using the Advanced Search and creating a query. The list of messages is shown, including just the lowest level folder name. I can navigate through the messages, and I'd like write a macro to show the full folder path of the currently selected (not opened) message, because I have many dozens of folders and they're not all named uniquely at the lowest level. Can this be done? Thanks tbone Show it where? Outlook version? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Program...