Tip:
Highlight text to annotate it
X
(narrator) Hi everybody, this is a recording for the review for module 3.
Again, to remind you, you can get your notes by going to Canvas and pulling down module
3 notes.
The files that are associated with this review are found in the link that says review.
This file you'll be using today will be the one called soybean.
So let's start with number 1, just says to open the workbook and save it.
Number 2 says the size of the soybean crop is 72 acres, enter this value in cell B4 of
the yield worksheet.
So we're going to the yield worksheet.
Cell B4, and we want to enter 72.
B4, in the yield worksheet we want to enter 72, and enter.
Ok, step 3, the soybean sample comes from a plot of 4 rows each, 7.5 inches wide, and 21 inches long.
Enter these values in the range B7 to B9.
Ok, so B7, the number of rows is 4.
B8, the width of the row is 7.5.
And B9, the length of the row is 21, and enter.
Ok, number 4.
Within the plot, the farm has harvested 400 soybean pods with an average of 2.5 soybeans per pod.
Enter these values in the range B14 and B15.
Ok, so B14 is the sample area, and they tell us that...
Hold on.
B14, come down to B14 the number of pods.
Ok, so they tell us that this is 400, and B15 which is the seeds per pod is 2.5, and enter.
Ok, number 5, apply the input cell style to the cells B4, B7 to B9, and B14 to 15.
So I'm going to select B4.
Hold down my control key and get B7 to B9.
Hold down my control key again and get B14 to B15.
And we want to apply the input cell styles, so up on our menu bar we have home, coming
over to the styles group, I want to drop this down.
Yours probably says cell styles right here, and when you click that you'll get the same
thing that I'm looking at.
Drop this down, and I'm looking for input.
So I'm going to click on the input cell style.
Number 6, use the equations described in the formulas worksheet, enter the following calculations:
A says in cell B10, calculate the area.
So let's come over here and look at the formula's worksheet, and we're looking for area, and
it tells us that the formula for that is area equals rows times length times width.
Ok, so let's go back to the yield worksheet, and we want to go to cell B10 and put in that formula.
So equals, and I'm going to pick B7, times B9, times B8, and hit enter.
Ok, step B, in cell B11, convert the sample area to acres by dividing the value in cell
B10 by the number of square inches in an acre, which is cell H4, and then display the result
to 4 decimal places.
So let's go to B11, where we're going to put our formula, and the formula will be equals
B10 divided by H4, so H4 and enter.
And then we want to make this 4 decimal places so go back to your ribbon bar at the top,
go to home, over to the numbers area, and we want to decrease that decimal.
So I'm going to click on this until I'm at 4 decimal places.
Ok, so that looks correct.
Ok, step C, in cell B16, calculate the number of seeds harvested in the sample.
Ok, so to get that formula, total seeds equals pods times seeds.
So let's go back to the yield worksheet, and we're putting this formula in cell B16, so
let's click on B16, and then the formula is going to be equals B15 times B14, and enter.
Ok, back to number 6, step D.
In cell B17, calculate the weight of the sample in pounds by dividing the number of seeds
by the number of seeds in one pound, which comes from cell H5.
Then display the value to two decimal places.
Ok, so let's go to B17, and put in our formula, equals, and that's going to be B16 divided
by H5, and hit enter.
And then take that to two decimal places.
Ok, step E.
In cell B18, convert the weight to bushels by dividing the weight in pounds, by the number
of pounds of soybeans in one bushel, which comes from cell H6.
Display the value to four decimal places, so we're going to B18, put in our formula,
so that's what it's going to be, B17 divided by H6, and enter.
And make this one four decimal places.
Ok, step F.
In cell B19, estimate the farm soybean yield in bushels per acre, by dividing the number
of bushels in the plot sample by the area of the sample in acres.
Display the value as an integer.
So, in B19, our formula's going to be B18 divided by B11, and enter.
And display it as an integer just means no decimals.
So I decrease my decimals.
Let's just go ahead and hit save on that.
Ok, step 7.
Calculate the following values for the soybean yield and revenue.
Step A in cell B20, calculate the farm's average soybean yield using the values in column E.
Use the round function to round that average value to the nearest integer.
So let's go to B20.
And let's first do the average.
So start our formula with average, open parenthesis, and what are we averaging?
We are averaging E4 to E25.
So E4, to E25, and then close that parenthesis.
Ok, let's come back up there because we have some more to do to that formula.
The other part of that says use the round function to round that average value
to the nearest integer.
So once I've selected it, I need to come up to my formula bar and click on it.
And we're going to rap the ROUND function around what we've already done.
So I'm putting my cursor between the equal and the A, and I'm going to type round, and
then open parenthesis, then I'm going to go to the end of that, and since I don't want
decimals, I know the syntax for that, and you can see the syntax there in the little
window that opens down below here.
I need to put a comma and then a zero for no decimals, and then close parenthesis and hit enter.
So let me get back and let you look at that.
So we first did the average, and then we wrapped the average once we had that with the ROUND function.
Ok, step B.
In cell B21, calculate the farm's median soybean yield from the values in column E.
So again, that's another function, so let's go to B21, and we want to use the median.
So, I can do equals, median, open parenthesis, and then what do I want to use?
E4 to E25, close parenthesis, and hit enter.
Ok, step C.
In cell B24, calculate the farms total production of soybeans in bushels, by multiplying the
bushels per acre value by the number of acres that the farm devotes to soybeans.
Display the value as an integer.
So in B24, our formula's going to be equals, B4, times, B19, and enter.
Let's go back up there because then we also need to say, display it as an integer, so
let's take away the decimals.
Ok, step D.
In cell B25, calculate the total revenue from the soybean crop by multiplying the total
bushels harvested by the current price per bushel, which comes from cell H7.
Display the value using the accounting format style.
So in B25, our formula's going to be equals, B24, times, H7 and enter.
And then we want to make that the accounting format.
So right now it's in the currency format.
So coming back up to the top to our numbers group.
We can drop this down and choose accounting.
And it didn't say anything about decimal places so we'll leave that.
Step 8.
Apply the calculation style to the range B10 to B11, B16 to B21, and B24 to B25, so let's
go ahead and select those ranges.
So B10 to B11, B16 to B21, and B24 to B25.
And apply the calculation style, so that's a cell style coming up to home.
Over here yours probably says cell styles.
You're going to drop it down, and you're looking for the calculation, cell style.
So now you're telling the user, if I click off of it, these that are formatted this way,
it comes from a calculation, these that are the orange color are input, where we actually
put data in.
So if they change any of these in orange, the calculation will also get updated.
Ok, number 9.
Use goal seek to determine what value in cell B4, the number of acres devoted to soybeans
will result in a total soybean revenue of $40,000.
So lets go to B4, and look at this.
Ok, so they want us to find out what value in B4 will result in a total soybean revenue
of $40,000.
So we're going to be changing B25 because we want that to end up being $40,000.
So we're going to use goal seek.
Ok, so we want to make sure we've selected B4 and we find goal seek by coming up to the
data tab over to forecast, this WHATIF, dropdown, and choose goal seek.
And here we have to set a few parameters.
So we want to set B25, which is our total revenue, so I'm going to click on B25, and
it changes that, to a value of $40,000 by changing what cell.
We're going to be changing B4, and hit ok.
And it goes to this calculation and comes back and tells us how many soybean crop acres
we have to yield to make a revenue of $40,000.
So there's our answer.
Ok, number 10.
In the growth worksheet, so let's go ahead and go to the growth worksheet.
In cell B5, we want to enter a formula with a function to display the current date.
Ok, so that function is called a today function, so equals today, open parenthesis, and hit enter.
So now every time this worksheet is open, it will display today's date in this area.
Number 11.
Use autofill to insert the text string day 1 through day 1/12 and the range D5 to D116.
Ok, so D5.
So in D5, we want to enter day 1, come down to D6, and enter day 2, and enter.
And then you highlight D5 and D6.
And then to use the autofill feature, if you come to the bottom corner over here, your
cursor changes to that little plus, I call that the big fat plus.
But you want to come down so it's just this little skinny plus, click on it.
And drag it all the way down to 116, row 116.
And when you get there, when you let go, it autofills in increments, each of those numbers
by one each time.
So that was autofill.
Ok number 12.
In cell G5, calculate the growing degree days, which is the GDP, for the first day of the
season using the formula, described in the formula's worksheet, and the temperature range
values in the range L6 to M9.
Hint, use the same formula used in the tutorial for corn, but enter the Tmin, Tmax, and base
values directly in the formula.
Be sure to use absolute references for the temperature range values.
Ok, so if we come over here to our formulas, here is where we're going to get this GDD
formula.
So it's equal to, Tmin plus Tmax, divided by 2 minus the base.
So what is Tmin?
Tmin is the minimum daily temperature.
Tmax is the maximum daily temperature.
And base is the baseline temperature.
So let's go back to our growth worksheet and see if we can do this formula.
So we're going to go to G5, the first one, and let's start our formula.
So equals, the first thing we want to do is get the max.
So, open, let's just do max and the part that we want to max is E5, so I'm going to point
to E5, comma, from M8 and then close parenthesis.
Plus the min from F5, comma, M9, close parenthesis.
Ok, so now we want to take that information and divide it by 2, so we need to put parenthesis
around all of this, because we want it all divided by 2.
And the bottom part is 2 minus the base, so 2 minus the base comes from M7, and click
on that.
Ok, so unless the last sentence there says be sure to use absolute references for the
temperature range values.
So they're wanting us to use absolute values on these over here, that we used in our formula,
so that when we copy the formula, they don't move.
Because right now we have it as a relative reference, and we want to make sure that we
make these absolute, so they're locked into place.
So let's come to M8, I'm going to double-click on it so it highlights it all.
And then on the keyboard, if you use F4, if you click it once, it goes ahead and puts
dollar signs around all of that.
That's what we want.
Then we want to come to M9, double-click.
Click F4 on the keyboard, and also M7.
And then you can either hit enter or click the checkmark to save all those changes to
that formula.
Ok, step 13 says copy the formula in G5 to the range G5 to G112.
That's an error, it should go all the way down to row 116.
So I'm going to copy what I have in G5, down to row 116.
Ok, there we go.
So now let's look at what absolute does for us.
So when I copy this formula down from row 5 to row 6, it changed the ones that didn't
have dollar signs around them, which meant they were relative.
Move them to row 6 for me.
But it kept the ones with the dollar signs absolute, it locked them into place.
So it didn't change.
Come down to the next one.
See the row 7 changed for me, updated my formula, so it used the information here from row 7
like it's supposed to.
So that's the way relative and absolute works.
Number 14.
Use the quick analysis tool to calculate the cumulative total of the GDD values from the
range G5 again this should be G116, placing those values in the range H5 to H116.
So first of all, we need to select the range G5 to G116.
Once we've selected them, down in the bottom right hand corner, we have this little icon.
This is our quick analysis tool.
So if I click that, it opens up.
I'll want to go to totals up here at the top, and then I want the running column.
So I'm looking for columns, see how the blue here that's showing a row.
So I want to keep moving on over and I can use this arrow to pushover more.
I want the running column.
So see how this shows columns right here?
So that's the one I want.
So click enter.
And that's using the quick analysis tool.
Number 15.
In B9, enter 5/12/2017, which is the data the farm will start planting the soybean crop.
So I'm going back up to B9, planning date.
And 5/12/2017.
Ok, in number 16.
In cell J5, enter a formula to display the date from cell B9, so let's just go ahead
and do tha first.
So let's go to J5, J5 over here.
And I want to display the date from B9.
So I'm just going to do equals, and you can either type in B9, or go point to it.
Point and click and it'll put it in, and hit enter.
In cell J6, enter a formula to increase the date in cell J5 by one day.
So to do that, we're going to hit equals, and I'm going to point to J5, go up to my
formula bar, and do plus one.
Let's see if that worked, it did.
So now 5/12, 5/13, has been increased by one day.
Now copy the formula in J6 to the range J6 to J again, 1/17.
So let's pick this one up and copy it all the way down to row 117.
Ok, and let's look at that.
It's increased the date all the way down by one.
Ok, step 17.
In cell B8, enter M070 as the maturity group for the current soybean hybrid.
B8, that's M070, enter.
Let's go ahead and hit save.
Number 18.
In the cell B10, use the VLOOKUP function to retrieve the cumulative GDD value for the
M070 hybrid.
Hint: the range L12 to M21 displays the cumulative GDD for each maturity group.
So before we start with this, let's look at a lookup table.
So first of all with a LOOKUP, you're taking data that you already have.
So in B10, that's where we're going to put our formula.
But we want to look up for M070, so that's what we're taking.
We want to look that up.
Let me just minimize my screen here some.
And we want to come over here to soybean types table, and we want it to go down through here
and read, and find M070, and bring back the cumulative GDD, which comes from column 2.
So that's the data that we're using and what we want to bring back.
So let's go to B10, and let's start our formula, and I like for the LOOKUP formula to use by
going to insert function, so click on the F(x) up here on the formula bar.
And come down and look for the VLOOKUP, if you don't have it, put it in the search box
and find it, and then click on it.
So we can see the arguments that we have here, so what do we need to fill in?
So the first thing it asks you is what is the LOOKUP value?
Well it told us that our LOOKUP value comes from B8, this M070.
So I'm just going to click on it, and it fills it in for me.
And you can also tell from this nice little arguments box if you're getting the right
data or not.
Look up here to the right.
See how it brought back M070?
So that's correct.
So let's go down to table array.
So now what they're asking you is where do I go find this data.
Well they told us to use this table over here, L12 to M21.
So if I L12 to M21, it fills it in for me, and you can see out here to the right, that's
the data that it's bringing back.
It's reading down here, within the table.
Now the next part is the column index number.
What do you want to return?
What do you want to bring back?
Well we want that cumulative GDD number that matches the M070.
So the way it works in Excel is you have to tell it which column is it coming from.
We selected a table and our table had 2 columns, but we want to return what's in column 2.
So I'm going to put a 2 there.
Now, let's look.
It brought back 1930.
Is that the correct answer?
It is.
But we also have one more item that we need to fill in down here.
It's a true false.
True is the default, which means that it's going to find the closest match.
Or false, the exact match.
We want the exact match.
So, we're going to put false in here.
And then click ok.
And it brought back 1930, which is the current answer because that's the one that's associated
with the M070.
Ok, number 19.
In cell I5, enter an IF function that tests whether the cumulative GDD value in cell H5
is greateer than the maturity value in cell B10.
So let's look at that first.
So our formula's going to go in I5.
Ok, so right here.
But we want to test whether the cumulative GDD value in H5, this one, is greater than
the maturity value in B10.
And B10 over here is 1930.
So those are the two that were testing with our IF function.
The rest of that statement says use an absolute reference to cell B10.
If the condition is true, return the text string ready, otherwise, return the text string
not ready.
Then copy the formula down that range.
Ok, so let's go to I5 where we are.
On the formula bar, click on the F(x), and we're looking for the function IF.
Ok, and in our functions arguments box, let me just move this so we can see it, remember
it told us we wanted to test whether the value in H5 is greater than the value in B10.
So, right here I'm just going to point to H5, use my greater than symbol, B10.
Ok, so there's my test, and it's telling me false.
So let's look.
9.5, is that greater than 1930?
No, it's not.
So that's why we have a false.
So if it is true, it said to put ready.
So since that's text, we're going to put it in quotation marks.
And if it's false, we want it to be not ready, also in quotation marks.
And one other piece to that said to use an absolute reference to the cell B10, this number.
And the reason that we're doing that is because when we copy this formula, remember how it
changes and it'll change the formula based on the row that you're on.
We don't want that number to change in B10.
We always want it to look at that same value so I remember absolute as locking.
So I want to lock that one so it doesn't move.
The H5 can move, but the B10 we don't want to.
So I'm going to double click on it so it highlights the whole thing, and then on my keyboard,
I'm going to use my IF 4 key.
And if I press it once, it puts the dollar signs around both the B and the 10, and that's
what I want.
And so I'm going to click, ok.
So now, it said to copy this down, so I'm just going to copy it down one, and just to
check our formula.
Now up here, remember we locked the B10.
Come down and click on the next one, notice how the H6 changed to be relative with the
row.
But the B10 stayed locked because we put the absolute values around it.
So that seems to be working properly, so let's just copy it down.
Ok, and let's check where would it change from not ready to ready while it would be
wherever that H5, or the number in H, the column H, became greater than 1930.
So if you scroll down here, let's check it.
Ok, that number's 1934 greater than the 1930, and it changed to be ready from not ready.
So that IF function is working properly.
Let's go ahead and hit save.
Ok, number 21.
No, 20.
Sorry 20.
In cell B11, insert a VLOOKUP function using the values in the columns I and J, that returns
the date on which the harvest ready value is first equal to the text string ready.
Ok, so let's take a look at this before we start building it.
We know we want to use a LOOKUP command, and the data that we're using or the table comes
from columns I and J.
So it's going to be reading down these columns, and it wants to bring back the date where
the harvest ready, column I, where that value changes to ready right here.
So we want to bring back the 8/15/2017.
So let's tell it how to do that.
First of all, where does our formula go?
It says B11, so let's click on B11, and start our VLOOKUP.
Ok, what value are we looking up?
Well, we want to lookup when that becomes ready.
The text becomes ready, so we're going to put ready in quotation marks here.
The table that we're using is just columns I and J.
I didn't change where my cursor was, let me come down to the table, and I'm just going
to say I and J, and it fills that in for me.
And then column index remember this is what we want to bring back.
So our table consisted of columns I and J, two columns.
Column I would be column one here, and J would be two.
We want to return the value that's in J, which is column two, so let's put a two there.
And let's look down here at our result, it's bringing back 8/31/2017, is that what we wanted.
Let's just go down and look.
Well no, it's 8/15/2017, but we haven't finished here.
So here we need to tell it either true or false.
Remember true is the closest match, which is the default if you don't put anything in,
so that's what it's assuming, that's why our numbers are not correct.
We want an exact match.
So we're going to say false.
And when I change that to false, notice how my number changes my formula result to 8/15/2017,
which is what I want.
So let's click ok.
And we now have 8/15/2017 in here.
Ok, number 21.
In cell B12, ok, right here.
Calculate the number of days between planning and harvesting by subtracting the planning
date, which is B9, from the harvest date, which is B11.
So this is just a simple formula, equals, B11 minus B9, enter.
95 days.
Ok, so that is it for module 3.
We've done some things in here, we need to make sure that you understand, make sure understand
VLOOKUPs, make sure you understand how an IF function works.
We also did a goal seek.
Make sure you go back and understand how to do goal seek.
And then just the rest of it was just some different formulas using round, median, average,
multiplication things, that type of thing.
So, again, go back and review any of that, that you might have questions with.