Giovanni asks how he can create a random multiple choice question; present a definition and four answers, one of which is right and three are wrong. While this sounds simple, the solution requires a mix of VLOOKUP, RANK, RAND, RANDBETWEEN functions. Episode 570 walks through the solution.
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:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question sent him by Giovanni.
This is gonna be tough. Let's see if we can get through the whole thing today.
Giovanni wants to set up a quiz he has a list of 25 definitions and the matching terms.
And like to present a definition and four of the terms kind of in a quiz format.
So I'm gonna come over here to the right hand side and see if we can build this on the fly, first thing we have to do is choose which of the 25 questions we're gonna present first.
I'm going to use the randbetween function, randbetween 1 and 25.
Now, of course if you're using Excel 2003, you have to use Tools, Add-ins and choose the Analysis Toolpak.
Before this would work.
Basically, so that's going to be the definition we're going to present.
And then the term, one of the four terms that we offer has to be the exact same number.
So that one's easy, but for the other items I want to kind of randomly choose, but, I want to make sure there are no duplicates And I don't want to have the right answer in there twice that would be horrible.
So, I'm going to use the right answer plus a randbetween Say 1 and 5.
Now, there's a possibility that if the question we're asking about is later in the list.
It's for it so, for example, We're asking about item 20, then we might have item 22, 24, 27 and that's beyond 25 So, the first thing I did is.
I took Giovanni's 25 line items and I copied them down so that way they run in rows 26 through 50 basically.
Certainly, what's gonna happen here is we're gonna offer four different answers, and we can use the index function. =INDEX( We'll go back to the original terms, and I'll include the extra 25.
I'll use the F4 key there and say that I want the second item.
So, this is, these are the set of answers that we're going to give for this particular question.
The question again will use the index there so we use the =INDEX(, this time I'll go back to the definitions and again, include the extra 50, hit the F4 key, and I'll use my random question as the index number.
So, now I have the definition.
Now, the last thing that I want to catch at this point.
I realize well wait a second, pretty soon people are going to realize that the right answer always comes first and the bad answers come after that.
So, we need some way to randomize that.
Now, I'm gonna go back.
This is a tip, that's in the book Learn Excel from MrExcel.
Basically, I might add a new column here with a random number.
So, we'll use =RAND() and then I'm going to rank those random numbers.
So, we'll use =rank(, of this random number within this whole set of random numbers and the beautiful thing about that is that, it's gonna give me a number from 1 to 4, and it's gonna be random every time.
So, when I set up my quiz.
I'm now going to say instead of just giving me these items in their exact order.
I'm gonna use an =VLOOKUP(.
So, that I want to find the number 1 rather than write the number one here though, I'm gonna put the row of A1, that's a easy way to say 1 and look within this range.
Starting with my rank over 5 columns.
I want the fifth column and false.
Now, the beautiful thing about using row of A1, is that as I copy that down to the other items.
it'll give me the row of A2, row of A3 ,row of A4.
So, the way that this works here we've chosen question 17 the amount of time.
We've chosen four answers one of which is write three of which are incorrect, but then because of the rank situation over here.
We've said that we're gonna put the correct answer as the second answer.
Basically, answer B And then the bad answer says A, C and D and down here, I use VLOOKUP to produce that quiz.
Every time we hit F9 to calculate, we get a new question new set of answers.
So, what sounded like a very easy thing to do for Giovanni required a lot of different techniques, using some randbetween, as we might have expected to choose the random items.
But, then also to make sure that we have no duplicates and that the answers are presented in a random sequence.
I used another rand function as well as the rank function and a vlookup.
Wow!
What a complicated thing to do for a Monday, but, anyway hopefully, there's a few tips in there that will help you out or if you need to build a quiz, just go back through the whole thing.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question sent him by Giovanni.
This is gonna be tough. Let's see if we can get through the whole thing today.
Giovanni wants to set up a quiz he has a list of 25 definitions and the matching terms.
And like to present a definition and four of the terms kind of in a quiz format.
So I'm gonna come over here to the right hand side and see if we can build this on the fly, first thing we have to do is choose which of the 25 questions we're gonna present first.
I'm going to use the randbetween function, randbetween 1 and 25.
Now, of course if you're using Excel 2003, you have to use Tools, Add-ins and choose the Analysis Toolpak.
Before this would work.
Basically, so that's going to be the definition we're going to present.
And then the term, one of the four terms that we offer has to be the exact same number.
So that one's easy, but for the other items I want to kind of randomly choose, but, I want to make sure there are no duplicates And I don't want to have the right answer in there twice that would be horrible.
So, I'm going to use the right answer plus a randbetween Say 1 and 5.
Now, there's a possibility that if the question we're asking about is later in the list.
It's for it so, for example, We're asking about item 20, then we might have item 22, 24, 27 and that's beyond 25 So, the first thing I did is.
I took Giovanni's 25 line items and I copied them down so that way they run in rows 26 through 50 basically.
Certainly, what's gonna happen here is we're gonna offer four different answers, and we can use the index function. =INDEX( We'll go back to the original terms, and I'll include the extra 25.
I'll use the F4 key there and say that I want the second item.
So, this is, these are the set of answers that we're going to give for this particular question.
The question again will use the index there so we use the =INDEX(, this time I'll go back to the definitions and again, include the extra 50, hit the F4 key, and I'll use my random question as the index number.
So, now I have the definition.
Now, the last thing that I want to catch at this point.
I realize well wait a second, pretty soon people are going to realize that the right answer always comes first and the bad answers come after that.
So, we need some way to randomize that.
Now, I'm gonna go back.
This is a tip, that's in the book Learn Excel from MrExcel.
Basically, I might add a new column here with a random number.
So, we'll use =RAND() and then I'm going to rank those random numbers.
So, we'll use =rank(, of this random number within this whole set of random numbers and the beautiful thing about that is that, it's gonna give me a number from 1 to 4, and it's gonna be random every time.
So, when I set up my quiz.
I'm now going to say instead of just giving me these items in their exact order.
I'm gonna use an =VLOOKUP(.
So, that I want to find the number 1 rather than write the number one here though, I'm gonna put the row of A1, that's a easy way to say 1 and look within this range.
Starting with my rank over 5 columns.
I want the fifth column and false.
Now, the beautiful thing about using row of A1, is that as I copy that down to the other items.
it'll give me the row of A2, row of A3 ,row of A4.
So, the way that this works here we've chosen question 17 the amount of time.
We've chosen four answers one of which is write three of which are incorrect, but then because of the rank situation over here.
We've said that we're gonna put the correct answer as the second answer.
Basically, answer B And then the bad answer says A, C and D and down here, I use VLOOKUP to produce that quiz.
Every time we hit F9 to calculate, we get a new question new set of answers.
So, what sounded like a very easy thing to do for Giovanni required a lot of different techniques, using some randbetween, as we might have expected to choose the random items.
But, then also to make sure that we have no duplicates and that the answers are presented in a random sequence.
I used another rand function as well as the rank function and a vlookup.
Wow!
What a complicated thing to do for a Monday, but, anyway hopefully, there's a few tips in there that will help you out or if you need to build a quiz, just go back through the whole thing.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
