# Computer COURSES

Document Sample

```					EXSC 408L                                                                                                         Fall ‘06
Introduction to Biomechanics
Lab 1 - Computer Skills

Lab #1 - Computer Skills Assessment Worksheet

Purpose:
Using Excel is an integral part of Exsc 408 Lab. If used properly, Excel will minimize the time you spend generating
data, performing calculations, and working on your project. Thus, it is to your benefit to master as many Excel skills
and shortcuts as possible. The purpose of this assignment is to help you develop a subset of Excel skills that will help
you to be successful this semester.

Note: Most operations in Excel can be performed in a number ways. The following procedures outline a few
methods. If you prefer a different way of performing the same task, that is fine, as long as the results are the same.

Procedure:
1. Open Microsoft Excel.
2. In cell A1 (column A, row 1), type “time”.
3. In cell B1 (column B, row 1), type “data one”.
4. In cell C1 (column C, row 1), type “data two”.
5. In cell D1 (column D, row 1), type “data three”.
6. Enter the following numbers in the given cells:
A2=1, A3=2, A4=3, A5=4, A6=5, A7=6
Because these numbers are in sequence, a quick way to do this is to the following:
a. Enter 1 into cell A2 and hit Enter.
b. Highlight cells A2-A7 (A6-A7 are still empty)
c. Under the ‘Edit’ menu select ‘Fill’ -> ‘Series’
d. Make sure the ‘Step Value’ is 1 and the ‘Type’ is ‘Linear’
e. Click ‘OK’
B2=1, B3=4, B4=3, B5=2, B6=2, B7=3
C2=4, C3=3, C4=2, C5=3, C6=3, C7=4
D2=0, D3=1, D4=2, D5=1, D6=4, D7=3
You now have six rows and three columns of data points.
7. Calculate the sum and average of each data column.
a. In cell A8, type "Sum". In cell A9 type "Avg".
b. In cell B8, type "=sum(B2:B7)".
c. In cell B9, type "=average(B2:B7)".
d. Copy and paste formulas from column B to column C.
i. One quick way to do this is to highlight cells B8 and B9. Type 'CTRL' -C (Control key + C at
the same time) to copy the formulas. Highlight cell C8 only. Typing 'CTRL' -V will then paste
the formulas into both C8 and C9. Excel will automatically change the column from B to C in
the formula and perform the calculations for data two. Highlight cell D8 and type ‘CTRL’-V to
paste the formulas into D8 and D9.
ii. Another way to do this is to highlight B8, B9, C8, C9, D8, and D9 and type 'CTRL' -R. This
command copies a formula into all of the highlighted cells to the right of it.
8. Plot your data points using two vertical axes:
a. Plot data one: Highlight the 'time' and 'data one' columns, include both the title and data (but not the
sum or average). Click on the 'Chart Wizard' button (button with bar graph). Under 'Chart Type' click
'XY (Scatter)'. Under 'Chart sub-type' click on the second graph in the first column, 'Scatter with data
points connected by smoothed lines'. Click 'Next'. You should see a preview of your graph, Click 'Next'
until you get to the ‘Chart Options’ dialog box. Add a creative chart title and axes labels. Click 'Next'.
Click on 'Add as object in' and 'Finish'.
b. Plot data two on the same graph: Highlight the time column. With the 'CTRL' key depressed, highlight
the 'data two' column. You will have the 'time' and 'data two' columns highlighted, but not 'data one'.
Copy this data using 'CTRL’-C. Next, click on your graph to select it. To add the data to the graph, go
to the ‘Edit’ menu and select 'Paste Special'. Follow the directions, making sure 'New Series',
'Columns', and 'Categories (X Values) in First Column' are chosen. Click ‘OK’. New points should be
added to the existing graph.

Page 1 of 3
EXSC 408L                                                                                                     Fall ‘06
Introduction to Biomechanics
Lab 1 - Computer Skills
c. Plot data three on the same graph: A second way to add a data series to a graph is to right click on one
of the lines on your graph. Select ‘Source Data…’. On the ‘Series’ tab, click on the ‘Add’ button. Now,
identify the values to be used on the X-axis by clicking on the icon next to the ‘X Values’ text box. You
can simply highlight the cells you want to use as your X values (A2:A7). Once you’ve highlighted the
correct cells, you should see ‘=Sheet1!\$A\$2:\$A\$7’ in the ‘Source Data – X Values’ dialog box. Hit the
‘Enter’ key to return to the ‘Source Data’ dialog box. Do the same thing for ‘Y Values’ using cells
D2:D7. Finally, type “data three” in the ‘Name’ text box, so that the data will be correctly identified in
the legend. Click ‘OK’.
d. Next, format the graph so there are two vertical axes: Double click on the last line you plotted (time vs.
data three), the 'Format Data Series' window will appear. On the ‘Axis’ tab, choose 'Secondary axis'.
You will see the numbers on the right axis. Click 'OK'. Now, change the value in D7 from 3 to 10 and
see what happens to the axis on the right as well as all of the data lines. (Note: Be careful when using
this option that you are not misrepresenting your data).
e. Change the X-axis to fit the range of data: Double click on one of the numbers on the X-axis to bring
up the ‘Format Axis’ dialogue box. On the ‘Scale’ tab, you can set the maximum and minimum values
that will be displayed on the X-axis as well as the increments of what will be displayed. Change
‘Minimum’ to 1, ‘Maximum’ to 6, and ‘Major unit’ to 1.
f. Add vertical gridlines to your graph: Right click in the ‘Chart Area’ (the white area around the actual
graph). Select ‘Chart Options…’. On the ‘Gridlines’ tab, check the box next to ‘Major gridlines’ under
‘Value (X) axis’.
g. Finally, double check that your graphs make sense by double-checking them with your data.
9. Calculate π divided by the sum of data two and data three for each row.
a. In cell E1, type "final data results".
b. Resize column E to fit the entire title in E1.
With cell E1 selected, click on ‘Format’ in the title bar and select ‘Column’ and then ‘AutoFit
Selection’. Or, move your cursor to the right border of the header for column E (where it says E).
When the cursor changes to a plus sign, you can click and drag the right border to make the
column the correct size.
c. In cell E2, enter an equation that will divide π by the sum of C2 and D2. Your equation should be
“=pi()/(C2+D2)”. (When writing a formula, you can click on a cell instead of typing its letter and
number).
d. Copy your equation from cell E2 to cells E3 through E7 by highlighting E2:E7 and clicking ‘CTRL’-D.
This command copies a formula down from one cell into all of the cells below it, changing the row
numbers appropriately.
e. In cell E8, copy over the equation from cell D8 to find the sum of column E.
f. In cell E9, copy over the equation from cell D9 to find the average of column E.
g. Note: Understanding how to write formulas and how to copy and paste them into new cells is
imperative for the labs you will complete this semester.
h. Note: When copying and pasting formulas, as above, you have seen that Excel automatically changes
the column letters and row numbers for you. Sometimes, this may not be exactly what you want.
PLEASE DOUBLE-CHECK FORMULAS GENERATED BY EXCEL.
10. Understanding the \$: Divide data two and data three by data one.
a. In cell F2, type ‘=C2/B2’. Highlight cells F2:F7 and type ‘CTRL’-D to copy this formula down through
F7. Highlight F2:F7 and G2:G7 and type ‘CTRL’-R to copy the formulas into column G.
b. Click on cell G2. You should see the new formula ‘=D2/C2’ in the formula bar because Excel
incremented the column letters when you copied the formulas from column F to G. This means that you
are dividing data three by data two, instead of data one.
c. To fix this, we need to tell Excel NOT to increment the column letters in the denominator.
d. In cell F2, type ‘=C2/\$B2’.
e. Copy the formula down and to the right as before.
f. This time, when you look in column G, the formulas will all be dividing by column B
g. In general, the dollar sign before the letter (i.e. \$B2) tells Excel NOT to increment the column letter.
Moving the \$ between the letter and number (i.e. B\$2) tells Excel NOT to increment the row number.
Using a \$ before both the letter and number (i.e. \$B\$2) tells Excel NOT to increment EITHER the

Page 2 of 3
EXSC 408L                                                                                                      Fall ‘06
Introduction to Biomechanics
Lab 1 - Computer Skills
column letter or row number. Use this last one if you have a constant number that is used frequently in
formulas across columns and rows, such as gravity.
11. Format your averages.
a. Highlight the cells with averages (B9:E9).
b. Under the ‘Format’ menu, select ‘Cells’.
c. On the ‘Format Cells’ tab, under ‘Category’, select ‘Number’. Make sure the ‘Decimal places’ text box
says ‘2’ and select ‘OK’. Your averages should all be displayed to the hundredths place now. (There
are also buttons on the toolbar that allow you to change the number of decimal places).
12. Use this worksheet as a template for different data
a. Copy this worksheet into a new worksheet in the same Excel file: Under the ‘Edit’ menu, select ‘Move
or Copy Sheet…’. Check the ‘Create a copy’ box and click on ‘OK’. To change the name of this sheet,
double click the tab in the bottom left corner that says ‘Sheet1(2)’ to highlight it and type in a new
label.
b. On your new sheet, change the data in column C to: C2=2, C3=3, C4=1, C5=0.5, C6=2, C7=2.
c. Notice that all of your formulas were recalculated, so the sum, average, and final data results all reflect
the new data. In addition, the graph was updated.
d. Change the title of your new graph.
e. Please use this concept throughout the semester when working with multiple trials. It will save
you time!
13. Displaying your formulas
a. Under the ‘Tools’ menu, select ‘Options. On the ‘View’ tab, check the ‘Formulas’ box in the ‘Window
Options’ section. Click ‘OK’.

Post-Lab:
1. Print out both worksheets (data and graph on the same page) and turn them in as your results section.
2. Remember to include your title page, purpose statement, and conclusion.

Page 3 of 3

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 28 posted: 4/26/2010 language: English pages: 3
Description: All kinds of: Method of experience in computer hacking skills in the maintenance of financial car stunt beauty slimming