I have always been fascinated by statistics. I'm trying to understand the math behind powerball statistics, but my spreadsheet experience is having trouble meeting the bill.
I'm trying to duplicate the statistics that I saw on http://www.simplypowerball.com/?q=node/33 , most simply the GMO column "number of games since last hit". So far, I imported the current powerball winning numbers text file from http://www.powerball.com/powerball/winnums-text.txt After import, the sheet '20120208' contains draws including 20120208 and has 8 cols from the original data-- A "DrawDate", B "WB1", C "WB2", D "WB3", E "WB4", F "WB5", G "PB", H "PP", and I added a column I "Draw DOW", which is simply a DOW number from the draw date in Col A. The value is either 4 for Wed, and 7 for Saturday. Plan to use that info later somehow. Next, I added another sheet which will scan the draws from first sheet, and report back the same stats from the SimplyPowerball webpage. The first task is getting the GMO column working. In order to do that, I need to understand how to lookup data in an array, report the row, and then to subtract a constant. Since the last draw is in row 2, I think I need to subtract 1. Yet I digress. >From sheet 'Pre-2008 WB' I'm trying to figure out how to scan array '20120208'.B3:'20120208'.F1491 row-by-row, and find the first row with a number that I'm looking for (which is in 'Pre-2008' in column A, which contains the sequential list of white balls 1 thru 55). I keep getting an error 502... which doesn't tell me what part of my function is invalid. I have tried LOOKUP function, MATCH function, and currently I am trying the HLOOKUP function. The current cell has the formula [=HLOOKUP(N(A2),'20120208'.B3:'20120208'.F1491,0)] . A2 contains the number 1, so I'm attempting to just find that a 1 has ever been drawn at the moment. Ideally, the formula should contain a ROW function (I think), and subtract 2... to show me before 2/8, when was a 1 drawn as a whiteball. I have thrown a copy of my current spreadsheet onto the web on my GoogleDoc account. the file link is http://bit.ly/wjJtXu . I will continue to fiddle with the formula, but if someone would be so kind as to teach me a little about how to find the row of the last time that a number was drawn... I would be grateful... because the error from Libre office doesn't really point me in a direction to fix the error. :) Thank you, -- Barry Smith e bnsmith...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted