I am trying to get some info for my lottery pick 6 to find out how long it has been since a lottery number hit with in a time frame of 6 months. This number can appear in any position with in a row BUT the Row that it is in corresponds to the date of that row with in a 6 month time period in my database. Example, Lottery database Cells A B C D E F G 1 7/27/2014 1 2 13 11 29 36 2 7/04/2014 7 15 21 32 33 53 3 6/25/2014 1 5 12 25 36 51 4 6/15/2014 4 10 17 21 22 49 4 5/30/2014 19 22 27 36 44 53 5 5/03/2014 1 2 3 14 25 46 Example, What I am trying to do for a reference Table Last 6 months of Activity Cells X Y Z Number Last Date 9 1 7/27/2014 10 2 7/27/2014 11 3 5/03/2014 12 4 6/15/2014 13 5 6/25/2014 I am using xl 2010 Here is my code that I am working with [=IFERROR(LOOKUP(2,1/(L$2:N$31=$A2),$K$2:$K$31),"") ]. This code does not refer to the above examples it is from my workbook... I keep getting "circular reference" warning OR "refers to empty cells", what am I doing wrong
I would like for this to be able to go back for the last 6 months of drawings and also would like for the range of dates to be expandable so that when I update the drawings which I do on a weekly basis that I will not have to go in and adjust the formulas to accommodate the recently added cells. Any help that any one can or is willing to provide to me on this matter will be greatly appreciated and a thousand kudos in advance. Thank You,
That's two columns, a date and one ball number per row, so each draw has 6 rows.
Give the table some headers; say Date, Number
You can add a third column, column C containing a formula in row 2:
=TODAY()-A2
and copy down. Give this column the header such as Days ago.
Then make the table an Excel table and call it Table1 (say).
The start will look like this:
[You've got some dates in the future (that's really good by the way! - if only…) which lead to negative numbers in the 3rd column, but it doesn't matter]
Then create a pivot table and make its source data Table1. With this you'll be able to get all sorts of statistics, including the last time a given ball(s) was/were picked in the last 6 months (183 days).
By creating a table, you'l be able to add data at the bottom and the formulae will automatically generate on the new rows and the table will expand, so the pivot table will also refer automatically to the new larger table.
In the attached there are two pivot tables, the left one shows "how long it has been since a lottery number hit with in a time frame of 6 months", the right one is filtered by the ball numbers 3,6,9,12,15,18, showing that you're going to win the jackpot on July 29th as it's showing those six numbers on that date. If you use the same numbers every draw, then it also shows you got two hits on June 25th, and two hits on May 3rd.
p45cal Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Reply With Quote
