Lotto Probability - 454 - Learn Excel from MrExcel Podcast

Scott from Australia sent in what should have been a simple lotto question. However, I chose to solve the problem using an array formula. Now, as far as I know, no one has ever tried to explain an array formula in a 2 minute podcast. Episode 454 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

---

Closed Caption:

hey welcome back to the mr. excel
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
voicemail us 8665 8 10 to 21 you have a
skype account you can leave my skype
voicemail bill jelen or in London 0207
8714 957 or just dropping email
Philip mr. excel com Scott has a
question today that involves something
very important time in work-related
especially if we're trying to get out of
work by hitting the lottery and Scott's
question is he has a list of most
frequent numbers that hit in the lottery
overtime 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
wants to know how many times those
numbers were in the list of most
frequent numbers now Scott proposed
using a series of six County formulas
that were all added together we can do
one better with a single formula called
an array formula array formulas are very
very powerful in Excel
and watches i build this the first thing
i want to think about is that I want to
end up with a string of six zeros are
ones ones one means that the number was
in the list 0 means the number was not
in the list now the array formulas going
to return those six numbers to me and at
the end i need to some those so that way
i can get the count of the number 1 i'm
gonna start my formula equals sum and
then use an if statement now I do a
match command match command says hey go
look for this number six within the list
and if there is a match returns the
number if there is no match returns and
na i'm really interested in the number
of na somebody use the function called
is an a is an a function and i'll put
the match function and usually we say
hey go match this number six with this
list but instead because we're using
array formula day going to do something
out of the ordinary i'm going to match
this entire string of six numbers this
range c4h for with the list of most
frequent numbers
there's my match command i'll use a
closing parenthesis for these na now if
i get an NA from that that means the
number is not found and I want to put a
zero 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
in Excel pro you'll say well this will
never work we have to use a secrets 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 we're in the most frequent numbers
copy that formula down and you'll see
that even though these numbers of the
most frequent numbers out of five weeks
this God sent me there was only one week
where you would have hit three of the
six members by betting those repeatedly
thanks to scott for sending that
question
tomorrow we'll take a look at a
different lottery related question how
to figure out what the most frequent
numbers are you tomorrow for another
NetCast from mr excel
ok