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!
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!
Transcript of the video:
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, bill@MrExcel.com.
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 MrExcel.
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, bill@MrExcel.com.
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 MrExcel.
