Today, Excel is offering all sorts of support for Geography, from 3D Map, Filled Map, Geography Data Type, and Location Data Types. But there was a secret project way back in 2005 that dabbled in Geography. In this entry for Excel Hash 2021, see how Sam Rad experimented with a function to identify cities along the I-7 corridor (Bakersfield California to Stockton California).
While the functions for the remaining interstates were never completed, you could use LAMBDA, or Power Query Custom Data Types, or FILTER to build your own.
This is my entry in the 2021 Excel Hash competition. Check out other entries here:
This playlist works on a PC:
This playlist works on mobile:
Check out John MacDougall's database of the 17 competitors and vote here: http://bit.ly/ExcelVotingTool
Thanks to Rob Collie who allowed us to talk about Interstate 7 before his Raw Data Podcast. Listen to episodes here: Raw Data Podcast - P3
While the functions for the remaining interstates were never completed, you could use LAMBDA, or Power Query Custom Data Types, or FILTER to build your own.
This is my entry in the 2021 Excel Hash competition. Check out other entries here:
This playlist works on a PC:
This playlist works on mobile:
Check out John MacDougall's database of the 17 competitors and vote here: http://bit.ly/ExcelVotingTool
Thanks to Rob Collie who allowed us to talk about Interstate 7 before his Raw Data Podcast. Listen to episodes here: Raw Data Podcast - P3
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2386.
From Bakersfield to Stockton.
Hey, welcome back to MrExcel Netcast.
I am Bill Jelen.
This is this year's episode of Excel Hash. This year many, many Excel MVP's are working on a video to go live February 3rd where we have to use four of these: Cut out people,.
LAMBDA, Spilled array, Power Query Data Types, the LET function.
Wait!
Stop!
Several of my friends pointed out that I have the wrong function here.
It’s the EVEN function.
I’ve already recorded Excel Hash.
And what is hilarious, in order to weave my story about this function, I did use the EVEN function.
Check it out in the second outtake at the end.
So, my entry for Excel Hash is using the wrong function, bit I hope you enjoy it anyway.
Just yesterday I did a recording for Rob Collie’s Raw Data podcast.
[ Bill to Rob Collie ] Alright so, Rob, you mentioned that the Lambda function.
There were specs for that going back to when you were on the Excel team?
[ Rob ] Yeah, 15 years ago.
And when I mentioned the LAMBDA function in Excel, Rob says, “it’s funny, I was on the Excel team in 2009 and he remembers seeing a spec for Lambda, right?
So that's wild that they were thinking about the Lambda function for 10 years, 11 years before it came to us.
And you know, there are functions that I'm sure they think about that never really fully reached their fruition.
And today I want I want to talk about one of those.
OK, so in California the corridor from Bakersfield, CA to Stockton, CA, there was a plan long ago to name that the I-7 corridor.
And, up in Seattle – there was also a plan, long ago, this was back before Excel 2007, where they were starting to think about geography.
Today we have all kinds of geography data types here under Data, you can either do Geography or Location and we can get all kinds of information.
But 15 years ago they were already thinking about it and you can see remnants of it.
So I have a whole bunch of California cities here and I've used =A5.Name.
Now these are not Power Query data types.
This is not part of Excel Hash, just getting basic data about these cities in here.
And then, check this out.
If you want to know if this city is along the I-7 corridor, all you have to do is use this function that never caught on.
The I-7 function.
And it will return False or True.
True along the I-70 corridor, or False if it's not.
And so what I've done here is, I've done a little data validation.
So we can choose a city over there from the left hand side.
Glendale, which is not on the I-7 corridor.
That's Angela, one of the Cut Out people.
You know everyone of the Cut Out people – (You find those on Insert, Icons, and then Cut Out people).
Every single person here has 20 different poses.
Some holding signs.
Some happy.
Some sad.
And so Angela there is telling you whether or not that particular city that you've chosen is along the I-7 corridor.
Right, now I hear what you're saying.
You're saying, Bill, why would they have something this specific?
One of the people on the Excel team grew up in Stockton?
So that's why they chose this.
And clearly, it never caught on.
[ Bill to Rob ] Do you have any history on … there was one guy in the Excel team from Stockton, CA and he was experimenting with geography way before Geography was a thing.
And he came up with that they were going to all the interstates.
And the first thing he did was the I-7 function.
Do you remember who that was?
[ Rob Collie ] I think it was Sam Rad.
Yeah, Sam Rad was really big on California geography.
[ Bill ] You are not believing me, are you?
You just don't believe that this is it.
So I'm going to take this data.
I'm going to go and use this I-7 column and in 3D Map.
Yeah, so here's that data in 3D Map.
The orange is anywhere where I-7 is equal to True.
And there it is.
From Bakersfield, right on up to Stockton.
All of those cities are accurately getting True from the iSeven and anything else that's not in the I-7 corridor is getting False for Blue.
So, clearly clearly it's working now.
It's kind of disappointing.
If your geography buff like I am.
I love Maps.
I love the Interstate system.
Why don't we have the other functions?
Why didn't they finish this?
Like for example, why is another function for iFive?
iNinetyFive?
iFourOhFive?
It would would be great, just be able to plug a city in and know what freeway that's along.
Well, the great news: because of Power Query data types and because of LAMBDA we can build those now today.
So over here I have in column A, a whole bunch more cities, not just cities in California.
Cities across the United States.
And Data Validation here that lets me pull that city up.
So we'll just choose a city, such as El Paso, TX.
And then, here check this out: =D2 (and D2 is a data type that's coming from Power Query) and I have fields, the iFive field, the iTen field, the iFourOhFive field.
That's the one around Seattle.
There's another 405.
It's down in California, but that that's known as “The 405”, right?
So, iTheFourOhFive.
Or iNinetyFive will tell you for each city that you choose which routes run through that.
So let's come over here to Jacksonville, FL – it is the crossroads of I-10 and I-95.
Alright, so that's using a Power Query data type.
We could also go the other way here where you choose a highway.
So I'll choose I-405 in Washington.
It runs from Bellevue and those cities.
Or if I would choose I-405 in California, those cities.
So you can choose the highway and see what cities are involved.
Now this one isn't directly using the Power Query data type, but it's using a spillable array.
Another one of our ingredients for Excel hash.
So I'm FILTERing, looking through the data for where the city has this highway.
So getting a different number of results depending on which highway you choose.
Longer highways, more cities, perhaps.
And then the last thing.
The newest thing.
The amazing thing.
The thing that Rob Collie said that Microsoft had been working on forever is the LAMBDA function.
So you know, Excel certainly has the iSeven function, But what if you really wanted a I5 function you don't want to have it stored in a data type, you just want it to be pure and beautiful and work like that.
So what I've done here is I've created the iNinetyFive function.
=iNinetyFive.
Point to one of these cells that has a city data type and it will return a one if that city is on I-95 or zero if it's not.
How is this working?
This is stored in Formulas, Name Manager, iNinetyFive.
Using the LAMBDA function.
We are passing in the city and then it's doing an XMATCH against the 95cities.
If that's an error, it's giving me a zero and then it's changing any of the results to a 1 if they are positive.
So you store that logic here in the name manager and just easy to call it from wherever.
Certainly easy to go through and maybe do that for all of the interstates.
You know, since we're talking about the US Interstate system, a really fun fact.
If you have a list of interstates and you need to figure out which ones go east-west, there is a great formula in Excel to do that.
You take the =MOD( the Interstate number, comma 2) and see if it's equal to 0.
Anything that is True is an East-West interstates.
Well, there you go.
That's my entry for Excel Hash.
Make sure to go watch the playlist and watch all of these other awesome MVP's struggle with how to use these various functions in some cohesive story.
If you like these videos, please Like, Subscribe, and Ring the Bell.
You won't find this story in my book MrExcel 2020, but you'll find a lot of other good things there.
I want to thank you stopping by will see you next time for another netcast from MrExcel.
[ Outtake: Bill to Rob ] So, you know, another function that that seems to have hatched back in that time period.
You might have been on the Excel team.
Do you remember which of the Excel team was from Stockton, CA into that experiment with the iSeven function?
[ Rob ] iSeven?
I don't even think.
Are there numbers?
Yeah, I guess there are numbers in function names, ACOS2?
[ Bill ] It's spelled out: I-S-E-V-E-N because otherwise it would have been cell I7.
I see why they did that, [ Rob ] Oh, I see, yeah, you wouldn't want to do it that way.
So I-7 spelled out?
Oh, do you mean the Is Even function?
[ Bill ] The what?
[ Rob ] Is Even.
[ Bill ] Never mind!
Outtake Number Two.
How I really did use EVEN for this.
Obviously, there is no iSeven function.
It is the ISEVEN function.
As I was trying to figure out how to manipulate the data to make this look right, I realized that I needed all of these cities up here that are along the I-7 corridor to have even numbers.
So when I used Equal Latitude times 1000, I wrapped that whole thing in the EVEN function to make these cities have an even number.
Like that.
And then I threw in a smattering of cities down here that were not along the I-7 corridor, and used the EVEN function minus one to make those all be odd.
And then sorted it alphabetically so it would be mixed in together.
And then, of course, the I-Seven function would return True on anything that is along the I-7 corridor.
The jokes on me, though.
Obviously, I was trying to mispronouce ISEVEN just to be a smart aleck.
But I really did get the function wrong.
It was EVEN, not ISEVEN.
Thanks to all my friends who pointed that out.
And thanks to you for watching this.
We will see you next time for another netcast from MrExcel.
From Bakersfield to Stockton.
Hey, welcome back to MrExcel Netcast.
I am Bill Jelen.
This is this year's episode of Excel Hash. This year many, many Excel MVP's are working on a video to go live February 3rd where we have to use four of these: Cut out people,.
LAMBDA, Spilled array, Power Query Data Types, the LET function.
Wait!
Stop!
Several of my friends pointed out that I have the wrong function here.
It’s the EVEN function.
I’ve already recorded Excel Hash.
And what is hilarious, in order to weave my story about this function, I did use the EVEN function.
Check it out in the second outtake at the end.
So, my entry for Excel Hash is using the wrong function, bit I hope you enjoy it anyway.
Just yesterday I did a recording for Rob Collie’s Raw Data podcast.
[ Bill to Rob Collie ] Alright so, Rob, you mentioned that the Lambda function.
There were specs for that going back to when you were on the Excel team?
[ Rob ] Yeah, 15 years ago.
And when I mentioned the LAMBDA function in Excel, Rob says, “it’s funny, I was on the Excel team in 2009 and he remembers seeing a spec for Lambda, right?
So that's wild that they were thinking about the Lambda function for 10 years, 11 years before it came to us.
And you know, there are functions that I'm sure they think about that never really fully reached their fruition.
And today I want I want to talk about one of those.
OK, so in California the corridor from Bakersfield, CA to Stockton, CA, there was a plan long ago to name that the I-7 corridor.
And, up in Seattle – there was also a plan, long ago, this was back before Excel 2007, where they were starting to think about geography.
Today we have all kinds of geography data types here under Data, you can either do Geography or Location and we can get all kinds of information.
But 15 years ago they were already thinking about it and you can see remnants of it.
So I have a whole bunch of California cities here and I've used =A5.Name.
Now these are not Power Query data types.
This is not part of Excel Hash, just getting basic data about these cities in here.
And then, check this out.
If you want to know if this city is along the I-7 corridor, all you have to do is use this function that never caught on.
The I-7 function.
And it will return False or True.
True along the I-70 corridor, or False if it's not.
And so what I've done here is, I've done a little data validation.
So we can choose a city over there from the left hand side.
Glendale, which is not on the I-7 corridor.
That's Angela, one of the Cut Out people.
You know everyone of the Cut Out people – (You find those on Insert, Icons, and then Cut Out people).
Every single person here has 20 different poses.
Some holding signs.
Some happy.
Some sad.
And so Angela there is telling you whether or not that particular city that you've chosen is along the I-7 corridor.
Right, now I hear what you're saying.
You're saying, Bill, why would they have something this specific?
One of the people on the Excel team grew up in Stockton?
So that's why they chose this.
And clearly, it never caught on.
[ Bill to Rob ] Do you have any history on … there was one guy in the Excel team from Stockton, CA and he was experimenting with geography way before Geography was a thing.
And he came up with that they were going to all the interstates.
And the first thing he did was the I-7 function.
Do you remember who that was?
[ Rob Collie ] I think it was Sam Rad.
Yeah, Sam Rad was really big on California geography.
[ Bill ] You are not believing me, are you?
You just don't believe that this is it.
So I'm going to take this data.
I'm going to go and use this I-7 column and in 3D Map.
Yeah, so here's that data in 3D Map.
The orange is anywhere where I-7 is equal to True.
And there it is.
From Bakersfield, right on up to Stockton.
All of those cities are accurately getting True from the iSeven and anything else that's not in the I-7 corridor is getting False for Blue.
So, clearly clearly it's working now.
It's kind of disappointing.
If your geography buff like I am.
I love Maps.
I love the Interstate system.
Why don't we have the other functions?
Why didn't they finish this?
Like for example, why is another function for iFive?
iNinetyFive?
iFourOhFive?
It would would be great, just be able to plug a city in and know what freeway that's along.
Well, the great news: because of Power Query data types and because of LAMBDA we can build those now today.
So over here I have in column A, a whole bunch more cities, not just cities in California.
Cities across the United States.
And Data Validation here that lets me pull that city up.
So we'll just choose a city, such as El Paso, TX.
And then, here check this out: =D2 (and D2 is a data type that's coming from Power Query) and I have fields, the iFive field, the iTen field, the iFourOhFive field.
That's the one around Seattle.
There's another 405.
It's down in California, but that that's known as “The 405”, right?
So, iTheFourOhFive.
Or iNinetyFive will tell you for each city that you choose which routes run through that.
So let's come over here to Jacksonville, FL – it is the crossroads of I-10 and I-95.
Alright, so that's using a Power Query data type.
We could also go the other way here where you choose a highway.
So I'll choose I-405 in Washington.
It runs from Bellevue and those cities.
Or if I would choose I-405 in California, those cities.
So you can choose the highway and see what cities are involved.
Now this one isn't directly using the Power Query data type, but it's using a spillable array.
Another one of our ingredients for Excel hash.
So I'm FILTERing, looking through the data for where the city has this highway.
So getting a different number of results depending on which highway you choose.
Longer highways, more cities, perhaps.
And then the last thing.
The newest thing.
The amazing thing.
The thing that Rob Collie said that Microsoft had been working on forever is the LAMBDA function.
So you know, Excel certainly has the iSeven function, But what if you really wanted a I5 function you don't want to have it stored in a data type, you just want it to be pure and beautiful and work like that.
So what I've done here is I've created the iNinetyFive function.
=iNinetyFive.
Point to one of these cells that has a city data type and it will return a one if that city is on I-95 or zero if it's not.
How is this working?
This is stored in Formulas, Name Manager, iNinetyFive.
Using the LAMBDA function.
We are passing in the city and then it's doing an XMATCH against the 95cities.
If that's an error, it's giving me a zero and then it's changing any of the results to a 1 if they are positive.
So you store that logic here in the name manager and just easy to call it from wherever.
Certainly easy to go through and maybe do that for all of the interstates.
You know, since we're talking about the US Interstate system, a really fun fact.
If you have a list of interstates and you need to figure out which ones go east-west, there is a great formula in Excel to do that.
You take the =MOD( the Interstate number, comma 2) and see if it's equal to 0.
Anything that is True is an East-West interstates.
Well, there you go.
That's my entry for Excel Hash.
Make sure to go watch the playlist and watch all of these other awesome MVP's struggle with how to use these various functions in some cohesive story.
If you like these videos, please Like, Subscribe, and Ring the Bell.
You won't find this story in my book MrExcel 2020, but you'll find a lot of other good things there.
I want to thank you stopping by will see you next time for another netcast from MrExcel.
[ Outtake: Bill to Rob ] So, you know, another function that that seems to have hatched back in that time period.
You might have been on the Excel team.
Do you remember which of the Excel team was from Stockton, CA into that experiment with the iSeven function?
[ Rob ] iSeven?
I don't even think.
Are there numbers?
Yeah, I guess there are numbers in function names, ACOS2?
[ Bill ] It's spelled out: I-S-E-V-E-N because otherwise it would have been cell I7.
I see why they did that, [ Rob ] Oh, I see, yeah, you wouldn't want to do it that way.
So I-7 spelled out?
Oh, do you mean the Is Even function?
[ Bill ] The what?
[ Rob ] Is Even.
[ Bill ] Never mind!
Outtake Number Two.
How I really did use EVEN for this.
Obviously, there is no iSeven function.
It is the ISEVEN function.
As I was trying to figure out how to manipulate the data to make this look right, I realized that I needed all of these cities up here that are along the I-7 corridor to have even numbers.
So when I used Equal Latitude times 1000, I wrapped that whole thing in the EVEN function to make these cities have an even number.
Like that.
And then I threw in a smattering of cities down here that were not along the I-7 corridor, and used the EVEN function minus one to make those all be odd.
And then sorted it alphabetically so it would be mixed in together.
And then, of course, the I-Seven function would return True on anything that is along the I-7 corridor.
The jokes on me, though.
Obviously, I was trying to mispronouce ISEVEN just to be a smart aleck.
But I really did get the function wrong.
It was EVEN, not ISEVEN.
Thanks to all my friends who pointed that out.
And thanks to you for watching this.
We will see you next time for another netcast from MrExcel.
