Excel Workshop - Directory



Lab #1 – Computing Skills

Today we have several tasks to accomplish.

➢ First, make sure your computer logon works correctly and you can access your N:/drive (to access your N:/drive remotely, open up a web browser and type ; enter your username and password)

➢ Second, make sure you can access RAMAS EcoLAB software (I will show you how)

➢ Third, become familiar with Excel as this will be a key program used to complete the labs in this course

In today’s lab we explore how to use some of Excel’s features. Your ability to manipulate data in this program will be paramount to your success in the lab portion of this course.

1. Enter the following data set into column A of Excel:

|1 |

|2 |

|3 |

|4 |

|5 |

|6 |

|7 |

|8 |

|9 |

|10 |

2. In Column B, Row 1, type the following: =(A1+1). You should now see the number 2 in cell B1. Copy this formula down the entire column. In Column C, Row 1, type the following: =(A1^2), you should now see the number 1 in cell C1. Copy this formula down the entire column. In column D, Row 1, type the following: =SQRT (A1), you should now see the number 1 in cell A1 (change the format of the cells to read only 2 decimal places). Copy this formula down the entire column. Sum the total of each column with the following = SUM(…) or hit the ∑ button.

At this point you should have a chart that looks like this:

|1 |2 |1 |1.00 |

|2 |3 |4 |1.41 |

|3 |4 |9 |1.73 |

|4 |5 |16 |2.00 |

|5 |6 |25 |2.24 |

|6 |7 |36 |2.45 |

|7 |8 |49 |2.65 |

|8 |9 |64 |2.83 |

|9 |10 |81 |3.00 |

|10 |11 |100 |3.16 |

|55 |65 |385 |22.47 |

3. A shortcut that will help with future labs is the $. The $ in Excel works like an anchor, if you place it before a column heading like $A, that anchors any information contained within column A, no matter where it is copied to. Likewise if you put at $ in front of row 1, it anchors that information. Experiment with the $ to see what happens when you copy and paste information from one cell to another.

** If you add a $ to your formula in the following fashion this is what you are telling Excel to do.

$A1 tells Excel that you always want to refer to column A

A$1 tells Excel that you always want to refer to row 1

$A$1 tells Excel that you always want to refer to cell $A$1

The above exercises demonstrate how to create formulas and copy them into the various cells of an Excel spreadsheet. There are many other calculations to be done using excel, the key is to remember to use the = sign or the function key to start the formula and parenthesis to capture the mathematical expression.

4. See what happens to your data when you go to copy and paste columns of data using $ and not using $. Also try to copy and paste your information using the paste and paste special features (try the values option and the transpose options to see what happens)

5. Click on the number 1 to left of cell A1, the entire row should now be highlighted. This will move everything down one row. You can enter column headings for each column in the spreadsheet.

6. Insert a row and label all your column headings, so that column A = weight, B = height, C = height sq, D = sqrt height, and E = IQ

7. In cell E1, type in an equation that represents this formula: IQ = weight * (height*sqrt height). When complete, copy the equation down the column to fill in the remaining empty cells until you get to row 10.

8. Let’s do some graphing.

Highlight columns C and D (except for the summed cells). Click the tab at the top of the screen marked . In here you will find graphing capabilities. Select and see what happened, which data ended up on your x-axis? Which one on your y-axis? Edit the graph by adding in axis labels.

When you have your scatter plot displayed, add a trend line to the graph that best fits these data points. To do this click one of the points in your graph, all points should now be highlighted. Now move the mouse to select “Add Trendline”. With your trendline added play around with the options such that you could “forecast” a trend forward or backward.

Forecasting is an important tool that we can use to make predictions from data that we have collected. Continue manipulating the data in the graphing portion until you feel comfortable with the graphical capabilities of the program.

9. Lets switch back to the spreadsheet and manipulate our simple data set a little more. Go up to the data icon, click it and then click sort. Sort the data in each column by descending and ascending values. Note what happens to the other cells that had formulas in them. Did this change your graph at all? How…explain.

10. To complete this exercise, email me (argent@calu.edu) a graph that you have created today with labeled axes containing at least one array of data that you entered. Fit a line to the data and forecast a reasonable number of units beyond the last data point to see your prediction and answer the question in line 9.

As you save this file use your last name and lab1 as the file name: for example, argentlab1.docx

OPERATORS

( ) = parentheses

+ = addition

– = subtration

/ = division

* = multiplication

^ = raised to the power of

STDEV = standard deviation

AVERAGE = mean

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download