Excel help needed ( Missing digits )

[serge]

Hi Everyone,

First question :

I have in cell A1 through A5 the following digits 2,3,5,8,9.
and I need formulas in A7 through A11 to give me the missing digits which would be for this example : 0,1,4,6,7.

Second Question :

Same thing but, can this be done also for more numbers from 1 to 56 ?
For example I have in A1 through A5 the following numbers : 2,12,34,44,53.
so the missing numbers would be all the numbers from 1 to 56 minius : 2,12,34,44,53.

I hope someone can help ? Thank you all.

 

[lottomaniac649]

Hi serge;
Both the tasks can be accomplished! Just you have to write the function to eliminate the draw results from the total number pool! e.g. If A1:A5 has the result then you enter in A7:A16 : =IF(COUNTIF($A$1:$A$5,1)>0,0,1)......IF(COUNTIF($A$1:$A$5,9)>0,0,9). Like that you've to enter from 1 to 56 for your second question. Hope, it will meet your requirement.
Bye!
Lottomaniac649

 

[serge]

Hi lottomaniac649,

Thank you for the quick respond, but it doesn't give me the result I need !!!
When I apply your formulas they give me the number I put in the formula !!!
I wrote :
=IF(COUNTIF($A$1:$A$5,1)>0,0,1)
=IF(COUNTIF($A$1:$A$5,1)>0,0,2)
=IF(COUNTIF($A$1:$A$5,1)>0,0,3)
=IF(COUNTIF($A$1:$A$5,1)>0,0,4)
=IF(COUNTIF($A$1:$A$5,1)>0,0,5)
=IF(COUNTIF($A$1:$A$5,1)>0,0,6)
=IF(COUNTIF($A$1:$A$5,1)>0,0,7)
=IF(COUNTIF($A$1:$A$5,1)>0,0,8)
=IF(COUNTIF($A$1:$A$5,1)>0,0,9)

And they give me the numbers 1,2,3,4,5,6,7,8,9 in the order of those formula, what I need is the numbers missing from that first list.
So if the numbers are : 2,3,5,8,9 like the previous post I need the formula to give me 1,4,6,7 ( ONLY ). Those would be the 4 missing digits if you take 1 to 9 as the base digits.

Thank you for your help.

Regards... Serge.

 

[Frank]

I think the problem is getting the missing digits into adjacent cells in the range A7 to A11 as you specify , if using formulas. Some pretty complex parsing would need to be done.

I can be done if you use all 10 cells A7 to A16 to house the missing digits and leave the appropriate columns blank.

For example you'd get.. 0,1,-,-,4,-,6,7,-,-, where the dashes represent empty cells.

Similarly for the 1 to 56 problem the missing digits would be distributed amongst 56 columns, 5 of them (the given numbers) being blank in the corresponding columns.
The only tidy way to do it as you specify, I believe would need a macro.

 

[PAB]

Hi Serge,

I know your request was a while ago now but if you are still looking for an answer here is one. This is an answer for your second question, simply adapt the formula for your first question.

I have in A1 through A5 the following numbers : 2,12,34,44,53.

As you have stated you have numbers in A1:A5, MOVE these numbers from A1:A5 TO B1:B5.
In cells A8:A63 enter the numbers 1 to 56.
In cell B8 enter this formula :-

=IF(SUMPRODUCT(COUNTIF($A8,B$2:B$6))=1,"",$A8)

Copy the formula down to cell B63 and then across as many columns as required.
Hope this helps.

[FONT=Verdana","sans-serif]Regards,[/FONT]
[FONT=Verdana","sans-serif]PAB[/FONT]
[FONT=Verdana","sans-serif] [/FONT]
[FONT=Verdana","sans-serif]-[/FONT]∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
[FONT=Verdana","sans-serif]12:45, restate my assumptions.[/FONT]
[FONT=Verdana","sans-serif]Mathematics is the language of nature.[/FONT]
[FONT=Verdana","sans-serif]Everything around us can be represented and understood through numbers.[/FONT]
[FONT=Verdana","sans-serif]If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.[/FONT]

 

[serge]

Hi PAB,

Thank you for the formula it work very nice.

Regrads,
Serge.

 

[PAB]

Hi Serge,

Hi PAB,

Thank you for the formula it work very nice.

Regrads,
Serge.

I am glad it worked and your request is now resolved.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[bruks]

Hi PAB!
If you use a simple spreadsheet Excel, let's do the follow:
1) The first four numbers “Lotto Max” 7/49 such as: 6, 23, 24, 42 I picked up: https://lottery.olg.ca/en-ca/winning-numbers/lotto-max/have-your-numbers-won?num1=5&num2=11&num3=22&num4=26&num5=1&num6=2&n um7=3
2) You need to set the numbers 1, 2, 3 that is the 5th, 6th and 7th numbers so that the win «Your numbers won «____» times» , was more than 48 times.
https://lottery.olg.ca/en-ca/winning-numbers/lotto-max/have-your-numbers-won?
You can see the steps of the calculation method in Excel.
Do You have the coordinates of a Registered User "Larbec": http://www.lottoforums.com/lottery/showthread.php/13271-The-Kob-7-system-for-Lotto-Max

Bruks

 

[bloubul]

bruks

PAP are for years no longer with us on this board.
I cannot find any excel spreadsheet at the link given by you.

If Larbec is still around he must respond by his own, we do not have email addresses of members.

BlouBul

 

[bruks]

Hi BlouBul!

Till now, I have not received a clear answer to my request. The question how to link the 4 numbers located in Excel, such as 6, 23, 24, 42 with the base:

https://lottery.olg.ca/en-ca/winning-numbers/lotto-max/have-your-numbers-won?num1=5&num2=11&num3=22&num4=26&num5=1&num6=2&num7=3

add 3 digits and get the result more than 48 times.

Yours sincerely

Bruks

 

[bruks]

Hi BlouBul!

Small correction, the base is located at the following address:

https://lottery.olg.ca/en-ca/winning-numbers/lotto-max/have-your-numbers-won?num1=6&num2=23&num3=24&num4=42&num5=1&num6=2&num7=3
Yours sincerely

Bruks

 

[Frank]

Hi BlouBul!

Till now, I have not received a clear answer to my request. The question how to link the 4 numbers located in Excel, such as 6, 23, 24, 42 with the base:

https://lottery.olg.ca/en-ca/winning-numbers/lotto-max/have-your-numbers-won?num1=5&num2=11&num3=22&num4=26&num5=1&num6=2&num7=3

add 3 digits and get the result more than 48 times.

Yours sincerely

Bruks

if I understand the question correctly, you can’t. I think you are wanting a website to be able to “see” four numbers you have in Excel , automatically put them into four input boxes on that site? That would be impossible. Excel linking only works in one direction, you can read a website in Excel , but a website cannot read your Excel spreadsheet. If you think about it, the website would need to know the Internet address of your computer, know where your spreadsheet was on your machine, look for a specific file, look for specific cells etc. Only the website developers can do that, and I don’t think you’d be safe if they could do it. Copy / paste doesn’t work either, again because they don’t want it to.