Excel Users: Help needed with Leap Froggers

[SHT]

Does anyone use Excel to obtain leapfrogger’s statistics? I’m trying to set-up automatic reporting of Leap Froggers from the previous draw.

My first option was to use the raw data from Lottostatistics’ trivia report, apply macros and formula to automatically parse and analyse the data. Unfortunately some numbers are not displayed as a two digit figure, hence if I was to parse the stats would be all wrong.
ex. 913
0710
079
940
060819

TIA
SHTH.

 

[johnph77]

SHT -

Have you tried entering the numbers with a lead zero (or zeroes) as right-justified labels? Then have your macros apply their conditioning to labels as well as numbers? This works with some applications, I don't know whether it'll work with your particular application or not.

 

[PAB]

Hi SHT,

If johnph77's Suggestions Don't Work.
You can Format the Cells with your Drawn Lotto Numbers in by Highlighting ALL the Drawn Numbers ( or the Whole Columns with the Drawn Numbers in ), Click Format, Click Cells, Select the Number Tab, Click Custom, in the Type: Box Enter Two Zeros ( 00 ) and then Click OK.
This Formats ALL the Drawn Numbers as Two Digit Numbers.

Hope this Helps.
All the Best.
PAB

 

[SHT]

solution

I forgot to say I resolved this.

Setup of automatic reporting of leapfroggers was deceptively simple once I switched on the brain cells. The reason for posting was that my initial instincts were complicating the problem and hence complicating the solution. I thought I somehow had to assign two “where it could of leapfrogged from” values to each current draw number and then somehow use LOOKUP to see if any of the assigned numbers were found in the previous draw.

But the solution is much much simpler than that, just need to use IF statements to look at each number and see if one up or one down is found in the previous draw.
My draw history =D:I

AX2=> {=IF(OR((D2-1)=$D3:$I3),D2,"")}
AY2=> {=IF(OR((D2+1)=$D3:$I3),D2,"")}
….
BI2=> {=IF(OR((I2+1)=$D3:$I3),I2,"")}

I then count the unique leapfrogs Col L and extract the leapfrogs to Col M.

I’m using a slightly different solution to the above.
Since at a later date I wish to study leapfroggers, I obviously will want the entire leapfrog pool for each draw (also saves time working out the pool to be used for filtering).
So AX:BI lists the potential leapfroggers for each draw.
AX2 =D2-1
AY2 =D2+1
……
BI2 =IF((I2)=49,"0",I2+1)

Col L=each draw counts how many (unique) leapfroggers it contains using the previous draw’s leapfrog pool. Col M= extracts the leapfroggers.

Hope that helps someone.
SHTH.