Today an idea from Tim in NH. While I frequently use RANDBETWEEN() to do a door prize drawing, Tim adds some bells and whistles to create a nice spreadsheet used to award the prize. Episode 726 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Frequently at my seminars, we give away door prizes and rather than hand everyone a slip a paper and draw their name out or get business cards.
It's easy just use Excel, we use the RAND between function.
The RAND between function will figure out, how many people are in the room.
Maybe 58 and we say ran between 1, 58 and that number corresponds to the winner.
So, here it's 38 of course, every time we press [ F9 ], we get a different answer.
So, if there's multiple prizes you can calculate again and again and again.
Got a nice spreadsheet from Tim Dolan.
Tim is a realtor up in New Hamisphere, and he says, frequently in his office.
They'll have drawings and like every time an agent brings in a listing.
They get an entry into the drawing.
So, as people bring in the listings over here in column A, just types of the names and it's possible that someone brings in multiple entries.
So, you know they might be entered a couple different times and it just keeps track and then at the end of the event when they're ready to have the drawing.
He has a couple of formulas in place, first of all this formula here in column B uses the COUNTA function.
COUNTA is similar to COUNT but COUNT only gets numbers.
COUNTA includes numbers and text.
So, he takes the COUNTA of all of column A and you'll notice that that tells us here that we have 12 cells, of course cell one is a heading.
We don't want to give the prize to that person and then in column C C2 has the word "NO" and down here in C3, is the formula that does all of the magic.
Basically, it says when we change C2 to be "yes", then we're going to use the index function.
The index function says look through column A and return a specific value.
Which value do we want to return?
Well, we want to return a random number between 2 and the total number of values y2 again, because we don't want to use that heading.
So, it's pretty cool.
It allows Tim to basically come along and type the word 'Yes', and then all of a sudden, we see that the winner is Josh.
Kind of a nice way to dress up the random between 1 and 58.
Very easy way to give door prizes away.
Takes a few minutes to set up and then you're good to go as you get new entries down Column A.
Eventually, the end of the night you just type in yes, and you have your winner.
So, thanks to Tim for sending it in and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Frequently at my seminars, we give away door prizes and rather than hand everyone a slip a paper and draw their name out or get business cards.
It's easy just use Excel, we use the RAND between function.
The RAND between function will figure out, how many people are in the room.
Maybe 58 and we say ran between 1, 58 and that number corresponds to the winner.
So, here it's 38 of course, every time we press [ F9 ], we get a different answer.
So, if there's multiple prizes you can calculate again and again and again.
Got a nice spreadsheet from Tim Dolan.
Tim is a realtor up in New Hamisphere, and he says, frequently in his office.
They'll have drawings and like every time an agent brings in a listing.
They get an entry into the drawing.
So, as people bring in the listings over here in column A, just types of the names and it's possible that someone brings in multiple entries.
So, you know they might be entered a couple different times and it just keeps track and then at the end of the event when they're ready to have the drawing.
He has a couple of formulas in place, first of all this formula here in column B uses the COUNTA function.
COUNTA is similar to COUNT but COUNT only gets numbers.
COUNTA includes numbers and text.
So, he takes the COUNTA of all of column A and you'll notice that that tells us here that we have 12 cells, of course cell one is a heading.
We don't want to give the prize to that person and then in column C C2 has the word "NO" and down here in C3, is the formula that does all of the magic.
Basically, it says when we change C2 to be "yes", then we're going to use the index function.
The index function says look through column A and return a specific value.
Which value do we want to return?
Well, we want to return a random number between 2 and the total number of values y2 again, because we don't want to use that heading.
So, it's pretty cool.
It allows Tim to basically come along and type the word 'Yes', and then all of a sudden, we see that the winner is Josh.
Kind of a nice way to dress up the random between 1 and 58.
Very easy way to give door prizes away.
Takes a few minutes to set up and then you're good to go as you get new entries down Column A.
Eventually, the end of the night you just type in yes, and you have your winner.
So, thanks to Tim for sending it in and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
