Lotto Probability – 454 – Learn Excel from MrExcel Podcast

Lotto Probability – 454 – Learn Excel from MrExcel Podcast

Hey, welcome back to the MrExcel netcast,
I’m Bill Jelen. Today’s question comes from Scott in Australia. If you have a question for the Podcast, please
feel free to send it in. You can call and leave it in as a voice mail
in the US, (866) 581-0221; if you have a Skype account, you can leave it in my Skype voice
mail, billjelen; or in London, 020-7871-4957; or just drop me an email, [email protected] Now, Scott has a question today that involves
something very important– I mean work-related– especially if we’re trying to get out of work
by hitting the lottery. Outstanding. Scott’s question is, he has a list of most
frequent numbers that hit in the lottery over time– and we’ll figure out how he got that
later, probably in tomorrow’s Podcast. But, today, each week he puts in the numbers
that came up in the lottery, he wants to know how many times those numbers were in the list
of most frequent numbers. Now, Scott had proposed using a series of
six Countif formulas that were all added together; we can do one better with a single formula
called an array formula. Now, array formulas are very, very powerful
in Excel, and watch as I build this. The first thing I’m going to think about is
that I want to end up with a string of six zeros or ones– a 1 means that the number
was in the list, a 0 means the number was not in the list. Now, the array formula is going to return
those six numbers to me, and then I need to sum those so that way I can get the count
of the number 1. So I’m going to start my formula with=SUM
and then use an IF statement. Now, when I do a match command– a match command
says, “Hey, go look for this number 6 within the list and if there is a match return the
number, if there is no match, return an NA. I’m really interested in the number of NA,
so I’m going to use the function called ISNA– ISNA function– and then I’ll put the MATCH
function. And, usually, we say, “Hey, go match this
number 6 with this list,” but instead, because we’re using array formula today, I’m going
to do something out of the ordinary– I’m going to MATCH this entire string of six numbers–
this range, C4 to H4– with the list of most frequent numbers. There’s my match command, I’ll use a closing
parenthesis for the ISNA. Now, if I get an ISNA from that, that means
that the number is not found and I want to put a 0– that’s my “then” part of the
IF function– otherwise, I want to put a 1. There’s the parentheses to close the IF function;
there’s the parentheses to close the SUM function. Now this big formula, if you’re an Excel pro
you’ll say, “Well, this will never work.” We have to use a secret set of keystrokes
in order to force Excel to know that this is an array formula. You have to hold down Ctrl+Shift and then
press Enter, and, sure enough, Excel tells us that three of the numbers from this week
were in the most frequent numbers. Copy that formula down and you’ll see that
even though these numbers are the most frequent numbers, out of five weeks that Scott sent
me, there was only one week where you would have hit three of the six numbers by betting
those repeatedly. Thanks to Scott for sending in that question. Tomorrow we’ll take a look at a different
lottery related question: How to figure out what the most frequent numbers are. See you tomorrow for another netcast from

3 thoughts on “Lotto Probability – 454 – Learn Excel from MrExcel Podcast

  1. How about a spreadsheet to check your ticket numbers. It can add all the numbers that match on each line of each game, and and tell you how many numbers and supplimentaries you have without you trying to remember the numbers as you check each line!

  2. on the newer version of excel this formula doesnt work. i even copy this sample and formula but it always return "1" value.

  3. =TOPLA(EĞER(EYOKSA(KAÇINCI(A2:V2;$AA$2:$AV$2;0));0;1)) ctrl+shift+enter Thanks to the excellent formula after many years …

Leave a Reply

Your email address will not be published. Required fields are marked *