Lecture3_Tutorial

1. Task 1: Use the 'IF' statement to find out the answer in the multi-plot problem in last lecture.

Copy the cells from the last problem into a new space. If you don't have the correct version, use the example here.

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.

2. Task 2: Import data from an external data file
For 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.

Create 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:

The 'COUNTIF' function means 'Count for 1 time if the referred cell satisfies the condition of ">0" (in this example). Definitely, it can be any other conditions you'd like to set.

5. Task 5: Plot the whether data.
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.

Then 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:

You 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'.
Click 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 statement. (Important)
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

The 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.