Mike's son Dennis has a baseball problem from his algebra class. Bill "MrExcel" Jelen and Mike "ExcelisFun" Girvin differ over whether the problem actually requires Algebra, Goal Seek, or simply a visit with Bob Barker. Pick up tips on solving Algebra Equations with Excel, using =AVERAGE, COUNTA, Goal Seek and more!
Transcript of the video:
Bill Jelen: Hey, welcome back. It's another dueling Excel podcast. I'm Bill Jelen from MrExcel.
We will be joined by Mike Girvin from Excel Is Fun.
This is Episode 85: Algebra.
We don't need no Algebra.
All right. Today's question is sent in by Dennis.
Dennis is my 15 year old son.
I'm guessing this came from a Math class, maybe even an Algebra class.
If over the last 11 years, a baseball player hits these number of runs, how many would he have to hit next year to have an average of 60?
Okay, so here's what we have to do.
We have to put in all those numbers that we already have, and then we're going to build a couple of formulas down here at the bottom.
See, I left year 12 blank to get the total, =SUM(B4:B15).
I’m going to start there in B15.
I'm going to use the mouse to highlight that whole range from B4 to B15, closing parenthesis, press Enter, and that will give us the total.
For an average, =AVERAGE(B12:B15), average like that opening parenthesis.
Use the mouse to highlight the range again including that blank cell at the bottom, press Enter.
All right, so we have this nice little spreadsheet set up with all of the past data, and we got to figure out what number to plug in here to make cell B17, B60.
You know, that's the problem with kids these days.
They didn’t spend their summers watching, The Price is Right, and Bob Barker giving away cars, shouting out higher or lower as people try to guess.
I mean, let's just guess 60.
That's too low.
Let's guess, 160.
Too high.
Let's guess 100.
Oh, we're getting really close.
99, 98 looks like the answer is 98.
How tough is that?
We don't need no Algebra.
Okay, but there is a faster way - the net.
Let's just go here to data, what-if analysis, Goal Seek.
Set cell E15 to...
Actually, no.
We want to set cell E17.
Let’s do that again, E17 to 60 by changing cell E15.
There we go.
We don’t have to plug in numbers, right?
Just click OK, Excel plugs in numbers there.
Bam, I got it.
It was tough that time.
It usually takes a second, it just gets it.
Actually, I had to guess a little bit.
Up down, up down, up down, and it got there.
So both methods lead to 98, I'm sure.
I'm sure there's a way to do this with Algebra.
Don't tell your Algebra teacher this, but you don't need no Algebra.
Mike, let's see what you have.
Mike Girvin: Thanks MrExcel.
Man, that Goal Seek is awesome.
Dennis is just going to zip through his homework from now on using Goal Seek to solve equations, where they're trying to solve for some unknown.
The great thing is, it’ll save so much time.
You can do more dishes.
Oh, of course, they'll probably just spend more time texting in Facebook, all right.
So here's the setup.
We had our question.
Very carefully, I put some labels here, 1 to 12.
I put the 11 numbers and that's what we're trying to solve for.
Now in order to solve this, we're first going to have to add up all of these.
So I'm going to come up here and use the SUM function.
All formulas start with an equal sign and now we'll use a built in function.
So =SUM(B4:B14), SUM, open parenthesis, and then you can click and highlight these cells.
Now be sure and highlight just the 11, not the 12th.
Once those dancing ants around the correct number of cells, Enter.
Okay, so we get 622.
If you click up here, and hit the F2 key, or double click, you can see.
Sure enough, that formula is correct.
Step two.
We need to define our variables and set up equation.
Now we're solving for number of homeruns in year 12.
So I'm going to say, Let X=# HR in Year 12.
Well, all right.
So we have an X in a 622 but we need a few other things for our equation, like this 60.
That's given, right?
So 60 is average after 12 years.
And I'm just going to type this in.
Oh, this is an average right?
Add them all up, divided by the COUNT.
So we need the COUNT, which is going to be Years, the total count including that year or 12.
So that's 12.
I could either type this in, or you get tricky here.
Now, I could use the COUNT function that counts numbers, or I could use COUNT.
Ah, that counts non-empty cells or words, right?
So I'm going to use that COUNTA.
I'll open parenthesis, highlight that range, dancing ants are going around the correct range, Enter.
=COUNTA(A4:A15) All right, finally we need our equation.
So equation, and this is all about the average.
Add them all up, divided by the COUNT.
Well, I don't have the total in year 12, right, but no problem.
In parentheses, I'm going to put (622+X)/12.
That's in the numerator, divided by, and then the denominator is our COUNT.
Now we are going to set this equal to 60 because we know that that's our goal.
Now I'm going to do a little trick here.
I need to repeat this down here, so I'm in edit mode.
I highlighted it with my eye beam, Ctrl C, and then Enter.
I'm going to come down here, and solve for this.
When you're solving for an X, you have two sides of the equation, and you can isolate that X as long as when you do something to one side, you do it to both sides.
So the first thing I'm going to do is, I need to get rid of that 12.
So I'm going to do a little tricky here.
I have to set up in advanced.
I'm going to multiply both sides by 12.
Now this looks a little bit odd in Excel because on the board, you have a big fraction bar but really, the 12 is in the denominator, and since we're doing multiply and this is all in the numerator, right?
Well we have a 12 in the numerator and a 12 and the denominator, those cancel and we're left with this.
So 12s cancel and we have this.
Now we have a 622+X. I need to get rid of that.
So I subtract 622 from both sides.
That on this side leaves me with 0, and our X will be isolated, and then we have our information on that side, all right.
So 622 is gone.
That's the trick to doing this in Excel.
You need to solve for that X, keep all those numbers there.
Now notice, we're going to build a formula right here.
That's a formula that's linked to this.
That's a hard coded formula input, and that also is a formula.
So now I'm going to come over here and just type this out.
X is what we want so, X=60*12-622.
Now when I hit Enter, I'm going to get the same 98 that MrExcel got, but mine took what, 25 times longer, right?
Goal Seek is the way to go but check this out.
This is dynamic.
If I come over here to year 5 and change this to 70...
Forget it.
No more steroids.
This is a 2011.
I'm going to change it to 45 before I hit Enter.
Watch, that will change, this will change.
Enter.
So oh, 111.
I'm going to undo.
Undo is up here.
That's our best friend.
Ctrl Z is the keyboard shortcut.
Not only that, but you can change this hard-coded input.
So if I want to change this to 45 or to...
Oh, I see that that won't work.
That would give us -82.
So that there is some lower limit to this.
So 60, we could type 59, we could type 62, all right.
We'll keep this as 60.
So absolutely awesome to set it up and do your Algebra in Excel because you can change your inputs but by all means, use a Goal Seek and save time.
All right, throw you back to MrExcel.
Bill Jelen: Well hey, Mike.
That was very cool.
I liked the Algebra, you know.
And hey, the important thing right now, if you're in Algebra class is that, when it comes time for the test, you're not allowed to use Goal Seek.
They want to know that you actually know how to use Algebra.
Well if you’re ever stuck in the middle of the desert without a computer, without Excel, you can solve for this stuff.
Might save your life someday.
Hey, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel and Excel Is Fun.
We will be joined by Mike Girvin from Excel Is Fun.
This is Episode 85: Algebra.
We don't need no Algebra.
All right. Today's question is sent in by Dennis.
Dennis is my 15 year old son.
I'm guessing this came from a Math class, maybe even an Algebra class.
If over the last 11 years, a baseball player hits these number of runs, how many would he have to hit next year to have an average of 60?
Okay, so here's what we have to do.
We have to put in all those numbers that we already have, and then we're going to build a couple of formulas down here at the bottom.
See, I left year 12 blank to get the total, =SUM(B4:B15).
I’m going to start there in B15.
I'm going to use the mouse to highlight that whole range from B4 to B15, closing parenthesis, press Enter, and that will give us the total.
For an average, =AVERAGE(B12:B15), average like that opening parenthesis.
Use the mouse to highlight the range again including that blank cell at the bottom, press Enter.
All right, so we have this nice little spreadsheet set up with all of the past data, and we got to figure out what number to plug in here to make cell B17, B60.
You know, that's the problem with kids these days.
They didn’t spend their summers watching, The Price is Right, and Bob Barker giving away cars, shouting out higher or lower as people try to guess.
I mean, let's just guess 60.
That's too low.
Let's guess, 160.
Too high.
Let's guess 100.
Oh, we're getting really close.
99, 98 looks like the answer is 98.
How tough is that?
We don't need no Algebra.
Okay, but there is a faster way - the net.
Let's just go here to data, what-if analysis, Goal Seek.
Set cell E15 to...
Actually, no.
We want to set cell E17.
Let’s do that again, E17 to 60 by changing cell E15.
There we go.
We don’t have to plug in numbers, right?
Just click OK, Excel plugs in numbers there.
Bam, I got it.
It was tough that time.
It usually takes a second, it just gets it.
Actually, I had to guess a little bit.
Up down, up down, up down, and it got there.
So both methods lead to 98, I'm sure.
I'm sure there's a way to do this with Algebra.
Don't tell your Algebra teacher this, but you don't need no Algebra.
Mike, let's see what you have.
Mike Girvin: Thanks MrExcel.
Man, that Goal Seek is awesome.
Dennis is just going to zip through his homework from now on using Goal Seek to solve equations, where they're trying to solve for some unknown.
The great thing is, it’ll save so much time.
You can do more dishes.
Oh, of course, they'll probably just spend more time texting in Facebook, all right.
So here's the setup.
We had our question.
Very carefully, I put some labels here, 1 to 12.
I put the 11 numbers and that's what we're trying to solve for.
Now in order to solve this, we're first going to have to add up all of these.
So I'm going to come up here and use the SUM function.
All formulas start with an equal sign and now we'll use a built in function.
So =SUM(B4:B14), SUM, open parenthesis, and then you can click and highlight these cells.
Now be sure and highlight just the 11, not the 12th.
Once those dancing ants around the correct number of cells, Enter.
Okay, so we get 622.
If you click up here, and hit the F2 key, or double click, you can see.
Sure enough, that formula is correct.
Step two.
We need to define our variables and set up equation.
Now we're solving for number of homeruns in year 12.
So I'm going to say, Let X=# HR in Year 12.
Well, all right.
So we have an X in a 622 but we need a few other things for our equation, like this 60.
That's given, right?
So 60 is average after 12 years.
And I'm just going to type this in.
Oh, this is an average right?
Add them all up, divided by the COUNT.
So we need the COUNT, which is going to be Years, the total count including that year or 12.
So that's 12.
I could either type this in, or you get tricky here.
Now, I could use the COUNT function that counts numbers, or I could use COUNT.
Ah, that counts non-empty cells or words, right?
So I'm going to use that COUNTA.
I'll open parenthesis, highlight that range, dancing ants are going around the correct range, Enter.
=COUNTA(A4:A15) All right, finally we need our equation.
So equation, and this is all about the average.
Add them all up, divided by the COUNT.
Well, I don't have the total in year 12, right, but no problem.
In parentheses, I'm going to put (622+X)/12.
That's in the numerator, divided by, and then the denominator is our COUNT.
Now we are going to set this equal to 60 because we know that that's our goal.
Now I'm going to do a little trick here.
I need to repeat this down here, so I'm in edit mode.
I highlighted it with my eye beam, Ctrl C, and then Enter.
I'm going to come down here, and solve for this.
When you're solving for an X, you have two sides of the equation, and you can isolate that X as long as when you do something to one side, you do it to both sides.
So the first thing I'm going to do is, I need to get rid of that 12.
So I'm going to do a little tricky here.
I have to set up in advanced.
I'm going to multiply both sides by 12.
Now this looks a little bit odd in Excel because on the board, you have a big fraction bar but really, the 12 is in the denominator, and since we're doing multiply and this is all in the numerator, right?
Well we have a 12 in the numerator and a 12 and the denominator, those cancel and we're left with this.
So 12s cancel and we have this.
Now we have a 622+X. I need to get rid of that.
So I subtract 622 from both sides.
That on this side leaves me with 0, and our X will be isolated, and then we have our information on that side, all right.
So 622 is gone.
That's the trick to doing this in Excel.
You need to solve for that X, keep all those numbers there.
Now notice, we're going to build a formula right here.
That's a formula that's linked to this.
That's a hard coded formula input, and that also is a formula.
So now I'm going to come over here and just type this out.
X is what we want so, X=60*12-622.
Now when I hit Enter, I'm going to get the same 98 that MrExcel got, but mine took what, 25 times longer, right?
Goal Seek is the way to go but check this out.
This is dynamic.
If I come over here to year 5 and change this to 70...
Forget it.
No more steroids.
This is a 2011.
I'm going to change it to 45 before I hit Enter.
Watch, that will change, this will change.
Enter.
So oh, 111.
I'm going to undo.
Undo is up here.
That's our best friend.
Ctrl Z is the keyboard shortcut.
Not only that, but you can change this hard-coded input.
So if I want to change this to 45 or to...
Oh, I see that that won't work.
That would give us -82.
So that there is some lower limit to this.
So 60, we could type 59, we could type 62, all right.
We'll keep this as 60.
So absolutely awesome to set it up and do your Algebra in Excel because you can change your inputs but by all means, use a Goal Seek and save time.
All right, throw you back to MrExcel.
Bill Jelen: Well hey, Mike.
That was very cool.
I liked the Algebra, you know.
And hey, the important thing right now, if you're in Algebra class is that, when it comes time for the test, you're not allowed to use Goal Seek.
They want to know that you actually know how to use Algebra.
Well if you’re ever stuck in the middle of the desert without a computer, without Excel, you can solve for this stuff.
Might save your life someday.
Hey, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel and Excel Is Fun.
