Microsoft Excel Tutorial: Using Excel to randomly draw a winner.
In this episode of the MrExcel netcast, Bill Jelen announces a giveaway of three pairs of Excel-themed socks and shares some insights about his YouTube subscribers. Bill initially encountered difficulty in obtaining subscriber data from YouTube due to privacy settings. However, he managed to gather data from 37,000 subscribers who had set their subscriptions to public.
Bill explores this subscriber data, revealing fascinating trends. He analyzes when his current subscribers first subscribed, noticing some who joined even before he began posting videos on YouTube in 2009. Bill also looks into how many subscribers his subscribers have, discovering that a significant portion of them don't create videos or have a minimal number of subscribers themselves.
Additionally, Bill mentions that he looked for people with "Excel" or related terms in their YouTube channel names, expressing his interest in their content.
The main highlight of the episode is the sock giveaway. Bill employs Excel's dynamic array functions, specifically SORTBY and RANDARRAY, to randomly select three winners from his subscriber list. The selected winners are Erick Dutra, Sharles Silva, and Gregor Čekon.
Bill invites the winners to reach out to him or keep an eye on their YouTube messages to claim their prizes. He also encourages viewers to subscribe, like, and ring the bell for more content.
In conclusion, Bill reflects on the success of the past month, with the return of the Dueling Excel podcast with Mike Girvin. He thanks his audience and looks forward to the next netcast.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
#excel
#microsoft
#exceltips
#microsoftexcel
#exceltricks
#microsoft365
#excelformula
#excelformulasandfunctions
This video answers these common search terms:
how do i do a random drawing from an excel spreadsheet
how to create a random drawing in excel
how to do a drawing in excel
how to do a random drawing in excel
how to use excel to make lucky draw
how to pick a random winner in excel
how to select a random winner in excel
Table of Contents
(0:00) Problem Statement: How to do Lucky Draw in Excel
(0:28) YouTube API to get subscribers list is broken
(0:53) Subscription has to be set to public for creator to see you
(1:30) 40% of my subscribers are public
(2:08) How many subscribers to my subscribers have?
(2:46) Art of being happy
(3:00) Subscribers with Excel in their channel name
(3:24) Choosing names from a hat but using Excel
(3:41) Pressing Ctrl+Shift+Enter
(3:55) Excel formula for random lucky drawing
(4:41) Winners of Excel socks
(4:55) Clicking Like really helps with the algorithm
How to use Excel to randomly pull names out of hat. You might win a cool pair of Office Insider socks in this video!
Official Contest Rules:
1. YouTube is not a sponsor of this contest. Winners release YouTube from any and all liability related to this contest.
2. You've entered the contest by being a public subscriber to my channel.
3. All prizes will be awarded.
4. Approximate retail value of each prize is US $4.50.
5. You must comply with all YouTube community guidelines or your entry will be disqualified. See: YouTube Community Guidelines & Policies - How YouTube Works
In this episode of the MrExcel netcast, Bill Jelen announces a giveaway of three pairs of Excel-themed socks and shares some insights about his YouTube subscribers. Bill initially encountered difficulty in obtaining subscriber data from YouTube due to privacy settings. However, he managed to gather data from 37,000 subscribers who had set their subscriptions to public.
Bill explores this subscriber data, revealing fascinating trends. He analyzes when his current subscribers first subscribed, noticing some who joined even before he began posting videos on YouTube in 2009. Bill also looks into how many subscribers his subscribers have, discovering that a significant portion of them don't create videos or have a minimal number of subscribers themselves.
Additionally, Bill mentions that he looked for people with "Excel" or related terms in their YouTube channel names, expressing his interest in their content.
The main highlight of the episode is the sock giveaway. Bill employs Excel's dynamic array functions, specifically SORTBY and RANDARRAY, to randomly select three winners from his subscriber list. The selected winners are Erick Dutra, Sharles Silva, and Gregor Čekon.
Bill invites the winners to reach out to him or keep an eye on their YouTube messages to claim their prizes. He also encourages viewers to subscribe, like, and ring the bell for more content.
In conclusion, Bill reflects on the success of the past month, with the return of the Dueling Excel podcast with Mike Girvin. He thanks his audience and looks forward to the next netcast.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel
#excel
#microsoft
#exceltips
#microsoftexcel
#exceltricks
#microsoft365
#excelformula
#excelformulasandfunctions
This video answers these common search terms:
how do i do a random drawing from an excel spreadsheet
how to create a random drawing in excel
how to do a drawing in excel
how to do a random drawing in excel
how to use excel to make lucky draw
how to pick a random winner in excel
how to select a random winner in excel
Table of Contents
(0:00) Problem Statement: How to do Lucky Draw in Excel
(0:28) YouTube API to get subscribers list is broken
(0:53) Subscription has to be set to public for creator to see you
(1:30) 40% of my subscribers are public
(2:08) How many subscribers to my subscribers have?
(2:46) Art of being happy
(3:00) Subscribers with Excel in their channel name
(3:24) Choosing names from a hat but using Excel
(3:41) Pressing Ctrl+Shift+Enter
(3:55) Excel formula for random lucky drawing
(4:41) Winners of Excel socks
(4:55) Clicking Like really helps with the algorithm
How to use Excel to randomly pull names out of hat. You might win a cool pair of Office Insider socks in this video!
Official Contest Rules:
1. YouTube is not a sponsor of this contest. Winners release YouTube from any and all liability related to this contest.
2. You've entered the contest by being a public subscriber to my channel.
3. All prizes will be awarded.
4. Approximate retail value of each prize is US $4.50.
5. You must comply with all YouTube community guidelines or your entry will be disqualified. See: YouTube Community Guidelines & Policies - How YouTube Works
Transcript of the video:
Learn Excel from MrExcel podcast episode 2363.
Let's give away some Excel swag. Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
I have three cool pairs of socks here with the Excel logo and the other Office products.
You won't believe how much hassle these three pairs of socks caused me this weekend.
The last time I had these, I gave them away on Twitter and it was fast.
This time, I thought, "Let's give them away to some of our YouTube subscribers.
That subscriber data is remarkably difficult to get from YouTube. There is an API. But it's not working.
People have been complaining for years that it's not working.
Google doesn't seems to want to fix it. What I have here 37,000 subscribers.
that I was able to get from YouTube. Subscriber, Date Subscribed.
How many subscribers they have to their channel.
URL and I have a few extra fields that I added out there to the right hand side.
Now, anytime that I get new data, I am excited to analyze the data.
The first thing to be aware of.
They only give me the names of subscribers who have their subscriptions set to Public.
By default, your YouTube subscription is not public.
You have to actually come to YouTube.
Click your avatar on the top right-hand corner. Go down to Settings. Inside of Settings, go to Privacy.
Then inside of Privacy, find Keep All My Subscriptions Private.
I initially figured this wasn't going to work because most people would have this set to Private.
I was surprised that out of 91,471 Subscribers this morning, there are 37 thousand - almost 41%.
have the privacy set so I can see their data.
This is fascinating because then we can look at trends like this.
When did my current subscribers first subscribe?
This shocks me. I've been creating videos since 2006.
but i didn't start posting them to YouTube until 2009.
There's actually a few people who subscribed in 2008.
I must have had a channel but I wasn't posting.
But yet I still had people who subscribed. So that's amazing right there.
And you can kind of see the growth of the subscribers over time.
The other one that struck me and I never would have thought of this.
But they gave me this data.
These are people who subscribe to me. But how many subscribers do they have?
I never thought about that.
First off, 55 or 56 percent of the people don't create videos. They don't have subscribers at all.
But then there's there's a whole bunch of people who it looks like they started a channel. Posted one or two videos. Got a couple.
three, four or five subscribers.
And then didn't continue or maybe they're just getting started. I am not sure.
Then you kind of go down through the number of subscribers that they have.
Yes, I am sitting right here in this 50 to 100K group. I realized "What am I concerned about?
Why am I trying to get more subscribers? This is a good number to have.
It's ahead of a lot of people.
So I am going to stop hassling you about subscribing. The other thing that I found.
I looked for people who had Excel in their name. Not just Excel, but grid or sheet or xl.
It was really fascinating to find all the people who have YouTube channels and are posting YouTube videos.
So if you are here in this list, there is a good chance that I visited your channel yesterday or today. I am now one of your subscribers.
There were a few people that really had nothing to do with Excel.
I may not have subscribed to them. All right let's go back here.
The whole goal today is to give away three awesome pairs of socks.
Thanks to the Office Insider team at Microsoft for passing these on to me.
In the old days I would have written all these names on tiny little slips of paper and put them in a really big hat. And then chose them out of the hat.
Thankfully we don't have to do that anymore.
Today I am going to use a combination of dynamic arrays to do this.
Joe McDaid said we'll never press Ctrl+Shift+Enter again.
But this is a case where I only want three results. So we are going to press Ctrl+Shift+Enter.
I am going to select the range where I want the results to be.
Not just one cell. I don't want it to spill.
I want to force it into three rows.
We'll do equal SORTBY. It is one of the new dynamic array functions. What do I want to sort?
I want to sort this entire list of names here. Out to column G. Do I have to press F4? No!
Because it's not being copied anywhere. What do I want to sort by?
I want to sort by a RANDARRAY.
We want to COUNTA of how many people we have today.
Let's take a close look at this. So I finished the COUNTA.
Close the RANDARRAY. Close the SORTBY.
Make sure that i have the right number of parentheses. I do.
I can quickly tell that because the last parentheses is black. Then press Ctrl +Shift+Enter.
And there are our winners. So Erick Dutra.
Sharles Silva and Gregor Čekon. What I am going to do is.
I am going to each of these channels and try and send you a message.
But if you happen to be seeing this or you know one of these people, reach out to me down in the YouTube comments.
I will find a way to get you those socks.
If you want to have a chance to win some socks, go click Subscribe down below.
Make sure that your subscriptions are set to public.
If you like this video, Click like and Ring that Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Well, that kind of wraps things up for August. We had a great month.
The dueling Excel podcast is back. Mike and I are already working on one for this Friday.
I want to thank you for stopping by.
We will see you next time for another Netast from MrExcel.
Let's give away some Excel swag. Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
I have three cool pairs of socks here with the Excel logo and the other Office products.
You won't believe how much hassle these three pairs of socks caused me this weekend.
The last time I had these, I gave them away on Twitter and it was fast.
This time, I thought, "Let's give them away to some of our YouTube subscribers.
That subscriber data is remarkably difficult to get from YouTube. There is an API. But it's not working.
People have been complaining for years that it's not working.
Google doesn't seems to want to fix it. What I have here 37,000 subscribers.
that I was able to get from YouTube. Subscriber, Date Subscribed.
How many subscribers they have to their channel.
URL and I have a few extra fields that I added out there to the right hand side.
Now, anytime that I get new data, I am excited to analyze the data.
The first thing to be aware of.
They only give me the names of subscribers who have their subscriptions set to Public.
By default, your YouTube subscription is not public.
You have to actually come to YouTube.
Click your avatar on the top right-hand corner. Go down to Settings. Inside of Settings, go to Privacy.
Then inside of Privacy, find Keep All My Subscriptions Private.
I initially figured this wasn't going to work because most people would have this set to Private.
I was surprised that out of 91,471 Subscribers this morning, there are 37 thousand - almost 41%.
have the privacy set so I can see their data.
This is fascinating because then we can look at trends like this.
When did my current subscribers first subscribe?
This shocks me. I've been creating videos since 2006.
but i didn't start posting them to YouTube until 2009.
There's actually a few people who subscribed in 2008.
I must have had a channel but I wasn't posting.
But yet I still had people who subscribed. So that's amazing right there.
And you can kind of see the growth of the subscribers over time.
The other one that struck me and I never would have thought of this.
But they gave me this data.
These are people who subscribe to me. But how many subscribers do they have?
I never thought about that.
First off, 55 or 56 percent of the people don't create videos. They don't have subscribers at all.
But then there's there's a whole bunch of people who it looks like they started a channel. Posted one or two videos. Got a couple.
three, four or five subscribers.
And then didn't continue or maybe they're just getting started. I am not sure.
Then you kind of go down through the number of subscribers that they have.
Yes, I am sitting right here in this 50 to 100K group. I realized "What am I concerned about?
Why am I trying to get more subscribers? This is a good number to have.
It's ahead of a lot of people.
So I am going to stop hassling you about subscribing. The other thing that I found.
I looked for people who had Excel in their name. Not just Excel, but grid or sheet or xl.
It was really fascinating to find all the people who have YouTube channels and are posting YouTube videos.
So if you are here in this list, there is a good chance that I visited your channel yesterday or today. I am now one of your subscribers.
There were a few people that really had nothing to do with Excel.
I may not have subscribed to them. All right let's go back here.
The whole goal today is to give away three awesome pairs of socks.
Thanks to the Office Insider team at Microsoft for passing these on to me.
In the old days I would have written all these names on tiny little slips of paper and put them in a really big hat. And then chose them out of the hat.
Thankfully we don't have to do that anymore.
Today I am going to use a combination of dynamic arrays to do this.
Joe McDaid said we'll never press Ctrl+Shift+Enter again.
But this is a case where I only want three results. So we are going to press Ctrl+Shift+Enter.
I am going to select the range where I want the results to be.
Not just one cell. I don't want it to spill.
I want to force it into three rows.
We'll do equal SORTBY. It is one of the new dynamic array functions. What do I want to sort?
I want to sort this entire list of names here. Out to column G. Do I have to press F4? No!
Because it's not being copied anywhere. What do I want to sort by?
I want to sort by a RANDARRAY.
We want to COUNTA of how many people we have today.
Let's take a close look at this. So I finished the COUNTA.
Close the RANDARRAY. Close the SORTBY.
Make sure that i have the right number of parentheses. I do.
I can quickly tell that because the last parentheses is black. Then press Ctrl +Shift+Enter.
And there are our winners. So Erick Dutra.
Sharles Silva and Gregor Čekon. What I am going to do is.
I am going to each of these channels and try and send you a message.
But if you happen to be seeing this or you know one of these people, reach out to me down in the YouTube comments.
I will find a way to get you those socks.
If you want to have a chance to win some socks, go click Subscribe down below.
Make sure that your subscriptions are set to public.
If you like this video, Click like and Ring that Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Well, that kind of wraps things up for August. We had a great month.
The dueling Excel podcast is back. Mike and I are already working on one for this Friday.
I want to thank you for stopping by.
We will see you next time for another Netast from MrExcel.
