If you haven't guessed by now, Bill is always up for a challenge - even if it's a simple one. Bill had this to say about today's Podcast:
"I get to meet all types of interesting people. Today, I met a realtor who sells about 240 houses per year. I tried to dust off my middle school Math in Excel to convert that Statistic to something more meaningful."
Today, in podcast 1723, Bill shows us how to use Excel to Cross Multiply and Cancel Units.
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
"I get to meet all types of interesting people. Today, I met a realtor who sells about 240 houses per year. I tried to dust off my middle school Math in Excel to convert that Statistic to something more meaningful."
Today, in podcast 1723, Bill shows us how to use Excel to Cross Multiply and Cancel Units.
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1723 - Unit Conversions by Cross-Multiplying and Canceling Units!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I used to really love math class, and over the years I've run into various math techniques that sometimes don't really work out very well in Excel, including something called canceling units.
So we have an original amount, you know, like 20, we make 24 sales in a week, and I want to know how many minutes does it take to make a sale.
Well, when we do this, we start multiplying the 24 by various factors, and I remember from math class, I can't remember where we learned this, that the next expression, since week is here on the bottom, has to start with weeks on the top.
So how many weeks per hour, and it's =1/40, so one week is equal to 40 hours, and then the next multiplication is, because I have hours on the bottom here, the denominator, it needs to be the numerator here, so how many hours per minute?
Well, that's =1/60.
And when I multiply 24*0.025*0.016667, I get 0.01 sales per minute, but that's not what I wanted, I wanted minutes per sale.
So, I set this up wrong, but the easy thing to do here, to flip this over, I want to see how many minutes per sale.
I just take =1/ that answer.
And so, if I'm making 24 sales per week, then every 100 minutes, assuming a 40-hour workweek, then there is a new sale.
You know, let's say that someone else is only making 10 sales per week, then they're only making a sale every 240 minutes.
Alright so, once I get the math right, I can continue to do "what if".
What if someone's doing a 100 sales per week?
That means that they're making a sale every 24 minutes.
And this came up today, I was interviewing realtors, and Jose Medina(?) stopped by, took a look at her house, we're trying to sell our house in Ohio.
And, I don't know a lot about real estate, but Jose threw an amazing number.
I mean, he's selling 230 houses per year!
Isn't that just a wild number?
And I was trying to do the math in my head to figure out if he's selling 238 houses per year, how many hours is he selling a house?
So let's try and set this up.
So we have houses per year, and I want to try and get that down to hours.
So I know that I have years on the bottom here, I want years on the top, and I could use the Equation Editor to create this in Excel, but I'm not going to do that.
I have the underline turned on here, so I'm just going to type Years, which creates my numerator, and it has the fraction line.
And now I press Alt+Enter to go to the denominator, and turn off underlining with Ctrl+U, and say Years and Hours.
So now this fraction is going to have one on the top, one year divided by hours, is going to be, let's see, 365*24.
Alright, so we get that number.
And let's do the math, now, so 238 houses per year, times that fraction, and we come up with how many houses per hour.
But that's backwards, I want to know how many hours per house, so we just do the flip, =1/ that fraction.
And it means that Jose is selling basically a house every 37 hours, which is amazing!
Now does that mean that he's going to sell my house in 37 hours?
Well no, because he has an inventory of many, many houses.
But still, it's an amazing number, if any of you out there have a realtor in the family, just check that out, see if they've ever heard of anyone selling 238 houses per year.
So a couple of things here, not just that Jose sells homes, but the ability to create a fraction with the text, and then cross canceling, like we used to do many, many years ago in school.
Whatever is on the denominator here, you want to have that in the numerator.
And if you wanted to actually go through, and make it look like we were canceling in the formula bar, you can choose those characters and Ctrl+5 to do the strikethrough.
And let's see up here Ctr+5 to do the strikethrough, and so on.
So let's just do some "what if".
What if someone, typical realtor maybe, selling 25 houses a year?
Then they're selling 1 house every 350 hours.
So once you get the maths up, you can do "what if", plug in a number in there, and get different answers.
It's kind of a fun little math project today, something going way back to, you know, maybe high school, I don't know where I actually pick this one up.
Cancelling units using Excel!
Why hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1723 - Unit Conversions by Cross-Multiplying and Canceling Units!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I used to really love math class, and over the years I've run into various math techniques that sometimes don't really work out very well in Excel, including something called canceling units.
So we have an original amount, you know, like 20, we make 24 sales in a week, and I want to know how many minutes does it take to make a sale.
Well, when we do this, we start multiplying the 24 by various factors, and I remember from math class, I can't remember where we learned this, that the next expression, since week is here on the bottom, has to start with weeks on the top.
So how many weeks per hour, and it's =1/40, so one week is equal to 40 hours, and then the next multiplication is, because I have hours on the bottom here, the denominator, it needs to be the numerator here, so how many hours per minute?
Well, that's =1/60.
And when I multiply 24*0.025*0.016667, I get 0.01 sales per minute, but that's not what I wanted, I wanted minutes per sale.
So, I set this up wrong, but the easy thing to do here, to flip this over, I want to see how many minutes per sale.
I just take =1/ that answer.
And so, if I'm making 24 sales per week, then every 100 minutes, assuming a 40-hour workweek, then there is a new sale.
You know, let's say that someone else is only making 10 sales per week, then they're only making a sale every 240 minutes.
Alright so, once I get the math right, I can continue to do "what if".
What if someone's doing a 100 sales per week?
That means that they're making a sale every 24 minutes.
And this came up today, I was interviewing realtors, and Jose Medina(?) stopped by, took a look at her house, we're trying to sell our house in Ohio.
And, I don't know a lot about real estate, but Jose threw an amazing number.
I mean, he's selling 230 houses per year!
Isn't that just a wild number?
And I was trying to do the math in my head to figure out if he's selling 238 houses per year, how many hours is he selling a house?
So let's try and set this up.
So we have houses per year, and I want to try and get that down to hours.
So I know that I have years on the bottom here, I want years on the top, and I could use the Equation Editor to create this in Excel, but I'm not going to do that.
I have the underline turned on here, so I'm just going to type Years, which creates my numerator, and it has the fraction line.
And now I press Alt+Enter to go to the denominator, and turn off underlining with Ctrl+U, and say Years and Hours.
So now this fraction is going to have one on the top, one year divided by hours, is going to be, let's see, 365*24.
Alright, so we get that number.
And let's do the math, now, so 238 houses per year, times that fraction, and we come up with how many houses per hour.
But that's backwards, I want to know how many hours per house, so we just do the flip, =1/ that fraction.
And it means that Jose is selling basically a house every 37 hours, which is amazing!
Now does that mean that he's going to sell my house in 37 hours?
Well no, because he has an inventory of many, many houses.
But still, it's an amazing number, if any of you out there have a realtor in the family, just check that out, see if they've ever heard of anyone selling 238 houses per year.
So a couple of things here, not just that Jose sells homes, but the ability to create a fraction with the text, and then cross canceling, like we used to do many, many years ago in school.
Whatever is on the denominator here, you want to have that in the numerator.
And if you wanted to actually go through, and make it look like we were canceling in the formula bar, you can choose those characters and Ctrl+5 to do the strikethrough.
And let's see up here Ctr+5 to do the strikethrough, and so on.
So let's just do some "what if".
What if someone, typical realtor maybe, selling 25 houses a year?
Then they're selling 1 house every 350 hours.
So once you get the maths up, you can do "what if", plug in a number in there, and get different answers.
It's kind of a fun little math project today, something going way back to, you know, maybe high school, I don't know where I actually pick this one up.
Cancelling units using Excel!
Why hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
