Lottery Results Check - Please Help :)

[webbooo]

Hello, wondering if anyone can help have been stuck at this for a few hours and is doing my brain in

Ive got some lottery numbers I'm starting to use on a regular basis but noticed on the web I could get a copy of previous results and thought it would be good to see if they had come up in the past but unfortunately I cannot find an easy way to do this, I did see some VBA stuff which was over my head I'm hoping there is an easy formula to do the trick,

I have a list of previous results C4:H671 as an example and I have my regular numbers in C1:H1. I'm trying to reference my numbers against the array to see if my numbers have been drawn and the total.

I tried the below with no joy

=COUNTIF(C4:H671,$C$1)+COUNTIF(C4:H671,$D$1)+COUNTIF(C4:H671,$E$1)+COUNTIF(C4:H671,$F$1)+COUNTIF(C4:H671,$G$1)+COUNTIF(C4:H671,$H$1)

any assistance would be appreciated, the only other stuff I could find suggested the conditional formatting which isnt what I'm after, I would like to be able to tally results

Thanks

Me

 

[xenou]

Hi,
Try it with just one formula first:
COUNTIF(C4:H671,$C$1)

If that works, then continue (it can be easier to have the formulas in 8 cells rather than in one cell - then just add up the eight individual results). As far as I can tell it should work. Sometimes formulas like this fail if the numbers are stored as text rather than as true numbers, or vice versa. True numbers can be added up and by default are aligned right. Text cannot be added up and is by default aligned left (but they can still be matched, as long as you are matching text to text).

I suppose it's incumbent upon me to add that lottery results are independent trials so the results of one lottery has no bearing on the next. A number is equally likely to be picked or not picked regardless of what happened yesterday - anyone who says otherwise is making money with their "lottery secrets".

ξ

 

[T. Valko]

Hello, wondering if anyone can help have been stuck at this for a few hours and is doing my brain in

Ive got some lottery numbers I'm starting to use on a regular basis but noticed on the web I could get a copy of previous results and thought it would be good to see if they had come up in the past but unfortunately I cannot find an easy way to do this, I did see some VBA stuff which was over my head I'm hoping there is an easy formula to do the trick,

I have a list of previous results C4:H671 as an example and I have my regular numbers in C1:H1. I'm trying to reference my numbers against the array to see if my numbers have been drawn and the total.

I tried the below with no joy

=COUNTIF(C4:H671,$C$1)+COUNTIF(C4:H671,$D$1)+COUNTIF(C4:H671,$E$1)+COUNTIF(C4:H671,$F$1)+COUNTIF(C4:H671,$G$1)+COUNTIF(C4:H671,$H$1)

any assistance would be appreciated, the only other stuff I could find suggested the conditional formatting which isnt what I'm after, I would like to be able to tally results

Thanks

Me

Try this...

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C4:H671,C1:H1,0)),{1;1;1;1;1;1})=6))

 

[xenou]

Oh duh - thanks ^^. Just noticed the numbers were in multiple columns.

 

[webbooo]

Thanks Xenou and T.Valko,

Xenou I found the countif in that scenario the numbers need to be in sequence etc if the numbers are in a reversed order it wont display the tally or it wont count them

Valko what you provided looks like a start thanks, it showed a number of xero meaning my 6 numbers hadn't come up in the past, i copied some numbers from an existing entry to my numbers and it changed to "1".

Any ideas how get it to show the number of my numbers displayed each draw rather than matching the whole amount, ie if all numbers come up it would show 6 or if it was 3 numbers it should show 3 etc rather than "0" for nothing and "1" for all 6



cheers

 

[T. Valko]

Thanks Xenou and T.Valko,

Xenou I found the countif in that scenario the numbers need to be in sequence etc if the numbers are in a reversed order it wont display the tally or it wont count them

Valko what you provided looks like a start thanks, it showed a number of xero meaning my 6 numbers hadn't come up in the past, i copied some numbers from an existing entry to my numbers and it changed to "1".

Any ideas how get it to show the number of my numbers displayed each draw rather than matching the whole amount, ie if all numbers come up it would show 6 or if it was 3 numbers it should show 3 etc rather than "0" for nothing and "1" for all 6



cheers

Oh, I see. You want a row by row result...

Try this:

=SUMPRODUCT(COUNTIF(C$1:H$1,C4:H4))

Copy down as needed.

 

[webbooo]

Thanks Valko,

that has changed it. At the moment I only have 1 row of my numbers, If I wanted to introduce additional 6 combination numbers etc how can I get that formula to include the additional numbers as a reference is that possible? rather than seperating it

ie
if I have 3 lots of different numbers etc

2.5.32.14.6.18
3.12.14.6.28.40
1.7.35.7.45.3

how can I reference these against the look array etc ie the C4:H671 rather than row by row? does that make sense I can send you a copy of the file if that would help, dont think I can attach here

cheers

 

[webbooo]

i think the first one worked well

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C4:H671,C1:H1,0)),{1;1;1;1;1;1})=6))

but anyway instead of displaying a "1" for correct match it can show a "6" for 6 numbers and "5" for 5 etc?

thanks again

 

[T. Valko]

i think the first one worked well

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C4:H671,C1:H1,0)),{1;1;1;1;1;1})=6))

but anyway instead of displaying a "1" for correct match it can show a "6" for 6 numbers and "5" for 5 etc?

thanks again

Can you post some sample data and tell us what result you expect?