Tell me your five favorite music acts and I will use Wolfram Alpha Data Types in Excel to guess your age. This is my second video showing the new Wolfram Alpha Data types in Excel.
Transcript of the video:
[ ♪ ] Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
[ ♪ ] Basically, we start out with massive amounts of data.
[ ♪ ] So, how are we going to analyze this? Well, let's fire up a pivot table.
[ ♪ ] and see if we can solve this problem.
Learn Excel from MrExcel podcast episode #2342. Can I guess your age in Excel using Wolfram Alpha Music Acts? Hey, welcome back to the MrExcel Netcast.
I am Bill Jelen.
Rocking the Springsteen on Broadway shirt for this episode.
Yesterday, Wolfram Alpha Data Types In Excel, First Look. After I recorded that and put it up on YouTube.
I was talking to Mary Ellen (MrsExcel).
I said "It's like Christmas getting all this new stuff".
I explained to her what it does and she came up with dozens of ideas of things we could do.
So I sent out a survey, saying "I need some data for an Excel video.
Who are your top five music acts?" Actually i started right here in the house.
We have three generations of Jelen;s.
My son Zeke who used to edit the videos. Then my dad a World War II veteran.
Then I sent it out to a few Excel MVPs who were able to respond.
And some people around the office.
Alright, so for everyone we have their top five.
Of course, my top number one is Bruce Springsteen and the E Street Band.
Now, what we are doing here: We are going to Data, Data Types, and these brand new data types.
They support music acts but it is not in the list.
So this is one where we are just going to use Automatic.
I have to tell you this was harder than I thought it would be.
Right here: Bruce Springsteen and the E Street Band. Nothing came up.
So I searched for Bruce Springsteen and There's Bruce Springsteen - a person.
That's not what I wanted.
i wanted the musical act To make sure that that was working I came out here and said = B2 dot language entity type.
I had to check all these because lots of times I got the person instead of the music act.
They say at Microsoft that if i were to search for "Bruce Springsteen Music Act" that i would have had better luck.
I'll have to try that at some point. All right, so...
Step one. All 55 bands or a couple that were repeated.
I've now successfully got them to be listed as a music act.
Right here there are 55 rows with 11 different names so I use the new UNIQUE (that's not new anymore) the UNIQUE function to pull the list of names here. Then a simple little index number 1.
And INDEX of H2#. Which gives us that whole range.
If I would add someone else that would work. I want the first person.
Alright now to get my list of five this is awesome that this works.
I used the FILTER function to get the list of five. This gave me five cells going down.
I ran that into the TRANSPOSE.
What I love about this is that it gave me not just the name of the band but the actual data type cell as well.
So all the data in the data type cell came over as the result of this formula. Which is wild.
Now you look at this and say "Okay this is just one cell.
how much is really there?" How much is there? It's crazy how much is there.
So down here i say =K2.Works. What songs have they created?
My Love Will Not Let You Down. Prove It All Night, Two Hearts.
Atlantic City. You are lucky i'm not going to sing anything to you.
But Iam going to press Control down arrow and it goes down to row 535.
I think earlier when i tried it in yesterday's video (you didn't see that page) but I had just Bruce Springsteen music act and there were over a thousand songs that came up.
Now, for each of these songs, remember it cascades. So now I am in K5.
Over here I say =K5 dot first release date.
And it tells me the date that the song was released.
Alright so for each person here I can get a list of all their songs and when that song came out.
Now, I am going to try and combine those two columns.
So let's take a look at these two formulas. =K2.works and then =K5 dot first release date. Here in Step Two. Check this out.
This is just awesome that this is working.
When i come here, =L2.Works.[ First release date ] and it brings me a list of all of the release dates of those songs.
I'm not seeing the song titles anymore. But that's okay. I know that's working.
From the previous sheet.
And there was one - it's really weird - Mary Ellen had Jimmy Buffett as as her one of her top five and out of the 350 songs there was one that came back with a #FIELD! error.
How could they not know when it was released? If they know that it's a song.
But okay i'll take that.
So I had to wrap the whole thing in IFERROR to get this to work. Alright, now follow along.
I now have a list of all the songs when the eagles released.
Randy Austin - the newest Excel MVP. Hey, Randy! Thanks for sending in your list.
What I did i said these dates.
Think about a typical rock band.
They start out with a couple of albums that are kind of obscure and then maybe around the third album they really hit it big.
Bruce Springsteen - it was Born to Run in 1975 that he really became famous.
Two albums before that, you know, not so much.
So I said I'm gonna take all of these dates here.
I am not going to average them.
Instead I am going to ask for the second quartile so 40% of the way through.
And that got me a series of dates and then I took the five favorite bands and I averaged those and converted it to a YEAR.
The year of the average.
Now unfortunately the annoying thing, here, is I had to type in each person and get their score over here.
There's the year.
You know it's kind of funny when I look at this data, it tends to, I don't know...
a lot of these people's age but it tends to kind of be like somewhere between high school and college.
At least for the people who I know what year they were born. Not always but just seems to work out.
I actually tried Data, What-If Analysis, Data Table and while you would think there's no chance that would work, It actually very slowly started crunching through.
But I was getting some errors because ...I don't know?
Maybe it was timing out or the #BUSY! error or something was causing it to not work. Kudos for even trying it.
but I realized that just wasn't going to work.
I probably should have knocked out some VBA to type in a value here and then record the score there. But anyway here are the 11 scores.
And then down here I sorted that data using these scores up here by the second column in ascending order.
So there's my dad - 98 - at the beginning of the list and my son in his 20s at the bottom of the list and when I look at this, again I don't know everyone's age but it really seems that they are almost in the correct order.
It might be one or two that are out of order but for the most part.
It is pretty crazy to take all of this data just based on your five favorite musical acts and it kind of gets you into the correct sequence.
Just so you can see how it's working let's change from Randy Austin to Mike Girvin from H4.
And we had five different bands for each of those bands we get a whole series of dates that the songs were released.
Figure out the the second quartile and then average it.
Giving us 1987.
These new cool Wolfram Alpha data types are are really amazing. Remember there's over a hundred data types.
Just just fabulous stuff.
Alright also I didn't bother to show the card yesterday.
If you click here you get the little card that kind of gives you some of the information that you're going to see. My complaint here is this is a little narrow.
So for example - Works.
"My Love Will Not Let You D" But, in fact there's over 500 songs there.
So the Card i think is becoming really much too small for how much data we're getting.
Plus each of those songs then leads to a release date and on and on and on and on and on and on.
Alright, please, down below the video click Like. Subscribe. And maybe ring that bell.
Feel free to post any questions or comments down in the comments below.
My new book MrExcel 2020 Seeing Excel Clearly.
Click that "i" in the top right hand corner for more information.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♪♪ ] Tips and tricks, get your spreadsheet fix from MrExcel.com[ ♪♪ ]
[ ♪ ] Basically, we start out with massive amounts of data.
[ ♪ ] So, how are we going to analyze this? Well, let's fire up a pivot table.
[ ♪ ] and see if we can solve this problem.
Learn Excel from MrExcel podcast episode #2342. Can I guess your age in Excel using Wolfram Alpha Music Acts? Hey, welcome back to the MrExcel Netcast.
I am Bill Jelen.
Rocking the Springsteen on Broadway shirt for this episode.
Yesterday, Wolfram Alpha Data Types In Excel, First Look. After I recorded that and put it up on YouTube.
I was talking to Mary Ellen (MrsExcel).
I said "It's like Christmas getting all this new stuff".
I explained to her what it does and she came up with dozens of ideas of things we could do.
So I sent out a survey, saying "I need some data for an Excel video.
Who are your top five music acts?" Actually i started right here in the house.
We have three generations of Jelen;s.
My son Zeke who used to edit the videos. Then my dad a World War II veteran.
Then I sent it out to a few Excel MVPs who were able to respond.
And some people around the office.
Alright, so for everyone we have their top five.
Of course, my top number one is Bruce Springsteen and the E Street Band.
Now, what we are doing here: We are going to Data, Data Types, and these brand new data types.
They support music acts but it is not in the list.
So this is one where we are just going to use Automatic.
I have to tell you this was harder than I thought it would be.
Right here: Bruce Springsteen and the E Street Band. Nothing came up.
So I searched for Bruce Springsteen and There's Bruce Springsteen - a person.
That's not what I wanted.
i wanted the musical act To make sure that that was working I came out here and said = B2 dot language entity type.
I had to check all these because lots of times I got the person instead of the music act.
They say at Microsoft that if i were to search for "Bruce Springsteen Music Act" that i would have had better luck.
I'll have to try that at some point. All right, so...
Step one. All 55 bands or a couple that were repeated.
I've now successfully got them to be listed as a music act.
Right here there are 55 rows with 11 different names so I use the new UNIQUE (that's not new anymore) the UNIQUE function to pull the list of names here. Then a simple little index number 1.
And INDEX of H2#. Which gives us that whole range.
If I would add someone else that would work. I want the first person.
Alright now to get my list of five this is awesome that this works.
I used the FILTER function to get the list of five. This gave me five cells going down.
I ran that into the TRANSPOSE.
What I love about this is that it gave me not just the name of the band but the actual data type cell as well.
So all the data in the data type cell came over as the result of this formula. Which is wild.
Now you look at this and say "Okay this is just one cell.
how much is really there?" How much is there? It's crazy how much is there.
So down here i say =K2.Works. What songs have they created?
My Love Will Not Let You Down. Prove It All Night, Two Hearts.
Atlantic City. You are lucky i'm not going to sing anything to you.
But Iam going to press Control down arrow and it goes down to row 535.
I think earlier when i tried it in yesterday's video (you didn't see that page) but I had just Bruce Springsteen music act and there were over a thousand songs that came up.
Now, for each of these songs, remember it cascades. So now I am in K5.
Over here I say =K5 dot first release date.
And it tells me the date that the song was released.
Alright so for each person here I can get a list of all their songs and when that song came out.
Now, I am going to try and combine those two columns.
So let's take a look at these two formulas. =K2.works and then =K5 dot first release date. Here in Step Two. Check this out.
This is just awesome that this is working.
When i come here, =L2.Works.[ First release date ] and it brings me a list of all of the release dates of those songs.
I'm not seeing the song titles anymore. But that's okay. I know that's working.
From the previous sheet.
And there was one - it's really weird - Mary Ellen had Jimmy Buffett as as her one of her top five and out of the 350 songs there was one that came back with a #FIELD! error.
How could they not know when it was released? If they know that it's a song.
But okay i'll take that.
So I had to wrap the whole thing in IFERROR to get this to work. Alright, now follow along.
I now have a list of all the songs when the eagles released.
Randy Austin - the newest Excel MVP. Hey, Randy! Thanks for sending in your list.
What I did i said these dates.
Think about a typical rock band.
They start out with a couple of albums that are kind of obscure and then maybe around the third album they really hit it big.
Bruce Springsteen - it was Born to Run in 1975 that he really became famous.
Two albums before that, you know, not so much.
So I said I'm gonna take all of these dates here.
I am not going to average them.
Instead I am going to ask for the second quartile so 40% of the way through.
And that got me a series of dates and then I took the five favorite bands and I averaged those and converted it to a YEAR.
The year of the average.
Now unfortunately the annoying thing, here, is I had to type in each person and get their score over here.
There's the year.
You know it's kind of funny when I look at this data, it tends to, I don't know...
a lot of these people's age but it tends to kind of be like somewhere between high school and college.
At least for the people who I know what year they were born. Not always but just seems to work out.
I actually tried Data, What-If Analysis, Data Table and while you would think there's no chance that would work, It actually very slowly started crunching through.
But I was getting some errors because ...I don't know?
Maybe it was timing out or the #BUSY! error or something was causing it to not work. Kudos for even trying it.
but I realized that just wasn't going to work.
I probably should have knocked out some VBA to type in a value here and then record the score there. But anyway here are the 11 scores.
And then down here I sorted that data using these scores up here by the second column in ascending order.
So there's my dad - 98 - at the beginning of the list and my son in his 20s at the bottom of the list and when I look at this, again I don't know everyone's age but it really seems that they are almost in the correct order.
It might be one or two that are out of order but for the most part.
It is pretty crazy to take all of this data just based on your five favorite musical acts and it kind of gets you into the correct sequence.
Just so you can see how it's working let's change from Randy Austin to Mike Girvin from H4.
And we had five different bands for each of those bands we get a whole series of dates that the songs were released.
Figure out the the second quartile and then average it.
Giving us 1987.
These new cool Wolfram Alpha data types are are really amazing. Remember there's over a hundred data types.
Just just fabulous stuff.
Alright also I didn't bother to show the card yesterday.
If you click here you get the little card that kind of gives you some of the information that you're going to see. My complaint here is this is a little narrow.
So for example - Works.
"My Love Will Not Let You D" But, in fact there's over 500 songs there.
So the Card i think is becoming really much too small for how much data we're getting.
Plus each of those songs then leads to a release date and on and on and on and on and on and on.
Alright, please, down below the video click Like. Subscribe. And maybe ring that bell.
Feel free to post any questions or comments down in the comments below.
My new book MrExcel 2020 Seeing Excel Clearly.
Click that "i" in the top right hand corner for more information.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♪♪ ] Tips and tricks, get your spreadsheet fix from MrExcel.com[ ♪♪ ]
