Task 1: Use the 'IF' statement to find out the answer in the multi-plot
problem in last lecture.
the cells from the last problem into a new space.
Pickup the marked cell as shown in the figure above. Type the function
shown in the block below, and autofill all the cells in the
column. Remember to change the addresses (coordinate) of your cells,
yours will be different from mine.
We use the 'IF' statement to figure out the results. The 'IF' statement
is an embedded function. The arguments in the function are
IF(condition, true, false). So in this example, if y>0, then use
v*t*cos(theta) to get the horizontal travel distance; otherwise, put a
'0' there, so you can clearly find out when you start getting the
series of zeros.
Then, let's use another function in Excel. 'Max()'. This function will
return the maximum value in the selected range. Because you filled the
cells with zeros when y<=0 (ball hitted the ground), and the
other x values are definitely larger than 0, so the maximum 'x' value
in that column will be the horizontal travel distance.
Task 2: Import data from an external data file
example you have a '.csv' data file (a normal data format) here. Download the
data file to a location that you can find it afterwords.
a new Excel file, click Data-From Text/CSV. Find the '.csv' file you
just downloaded. Then click 'load' to load the data to Excel.
You should see
something like this:
Select the first data cell (left top conor):
Go to View-Freeze Panes, select the following option:
By doing this, you will freeze the first row the the first column.
Scroll your mouse up/down and left/right, you will always be able to
see the 1st row/column.
3. Task 3: Use the
'average()' function to find the average value in that range.
Scroll down to the bottom of the table, select one cell somewhere in
the region marked in the figure below.
Use the 'AVERAGE()' function to calculate the average tempterature in
July. (only select the data happened in July)
4. Task 4: Use the
'COUNTIF' function to find out how many days had precipitation.
Find the last column in this table, which is the 'Precipitation'
column. Type the function as shown below:
'COUNTIF' function means 'Count for 1 time if the referred cell
satisfies the condition of ">0" (in this example). Definitely,
can be any other conditions you'd like to set.
5. Task 5: Plot the
Let's plot the 'Temperature Low', 'Temperature High', and 'Temperature
Average' in the same plot.
Let's instert a 'scattered line' this time.
Plot the data and add a dashed line for the scattered data.
Right Click the empty area of the figure, go to 'Select Data' again to
add more series (which are Temperature low and Temperature average). In
the same way, change the line type into a dashed line.
Do the same thing for your 'Temperature Low'. And add titles for your
axises and the chart to finish the data plotting.
Before we go the next task, let's put a legend into this plot:
will see the three series don't have the correct name yet. Let's change
it. Right Click the emptpy area of the figure, click 'Select Data'.
the 'Series1', Click Edit, and type the name you'd like to show up in
the legend. Change the name for all the three series.
Now, the figure is ready to be presented.
The font size was changed here: (You need to select the text you want
to change before you change the font size here)
6. Task 6: Nested IF
Open a new worksheet in the same Excel file:
Download the student grade data file here. Un-zip the
file and put it in an appropriate location that you are familiar with.
Load the data using the similar way that you loaded the weather data.
Autofill the average score for all the 5 homeworks for each student.
Get the average as you did for the weather problem.
Add a column after the 'average' column. Type the Nested function like
shown in the following
function I used here is trying to give grades like this: If the score
is greater than 90, then "A"; If it is greater than 80, then "B";
Otherwise, all other scores will be an "F".
You will do the
similar problem in your homework 3. But the grade should be given in a
more reasonable manner (A, B, C, D, and F....). You will see the
instructions in the homework assignment. Just add more nested IF
statement there, and autofill the cells.
Complete all the tasks above in ONE excel file. Use different
worksheets to separate these tasks.
Send your excel file (along with your HW3) to email@example.com