Intro. to Spreadsheets: Top Ten Athletes
Assignment #1
20%
Spreadsheets allow you to gather and calculate data quickly and easily. The power of spreadsheets lie in their FORMULAS! A formula is a set of instructions that tell the spreadsheet what calculations to make. Once the formula is in place, the instructions will be carried out. If numbers are changed after the formula has been given-- the calculations will still be done.
There are many, many possible formulas! We will only use a few for this activity, but the principle is the same for all of them. The most famous spreadsheet is Microsoft's EXCEL! We will be using a similar program called Open Office Calc.
PART 1: Highest Paid AthletesSpreadsheet Assignment #1
20%
Creating your Spreadsheet:
*Note: Open Office and Microsoft Excel both use what is called a FORMULA BAR (found just above the spreadsheet). Whatever you type in a cell is SHOWN in this Formula bar. You can select/hi-light and edit in this Formula Bar and it will change what is found in the cell
Once again, to change font color->find the capital A with a line under it up on the Formatting Tool Bar.)
*Note: Open Office and Microsoft Excel both use what is called a FORMULA BAR (found just above the spreadsheet). Whatever you type in a cell is SHOWN in this Formula bar. You can select/hi-light and edit in this Formula Bar and it will change what is found in the cell
Once again, to change font color->find the capital A with a line under it up on the Formatting Tool Bar.)
a. In cell A1: type your first and last name. Select/Highlight your name and look up in the Formula
Bar and change the size of your name to 14, change the color to BLUE, and make your name BOLD .
b. In cell A2 : type Spreadsheet Introduction and make it SIZE 12 and BOLD
c. In cell A10: Type the words: Top Ten Highest Paid Athletes of 2009: . Make the letters RED, BOLD and SIZE 14!
d. Get a Top Athletes DATA sheet from Mr. K. It has the information you will need.
e. Rearrange / Resize your desktop:
Adjust the size of your window, so you can see BOTH your spreadsheet and this tutorial at the same time. You'll only
need to see columns A,B,C,D & E of your spreadsheet.
f. Resize your columns:
You will need to resize columns to fit in all your data. Click ONCE on the letter A on top of your spreadsheet to select the whole column. Then, go WAY up top of your screen and select FORMAT--> COLUMN WIDTH and change the width to 1.5 inches and hit OK. Repeat this same change for columns B,C,D,E & F making them all 1.5 inches!
g. Insert Headings: (exactly as they are below)
In cell A12 type: Name In cell B12 type: Sport In cell C12 type: Earnings
In cell D12 type: 5 Yr. Earnings
Select/Hi-light these 4 headings and change their color to BLUE. Also, CENTER and UNDERLINE them and make them BOLD! (You can do this individually OR you can click on the #12 on the left of cell A12 and select the whole row at once.)
h. Insert Names: (Use paper data sheet)
Start in cell A14 and type the first name on the list--> Tiger Woods. Then continue typing names down column A with Kobe Bryant in A15. Continue down the list until you get to Dale Earnhardt Jr. in cell A23. None of these names need to be bold!
i. Insert Sport Headings:
In Column B you will type the sport that each person is most known for. So in B14 Type in the Sport that Tiger Woods is most famous for → Golf. Use a Capital letter to begin each sport.
Now in cells B15 to B 23 fill in the sport for each person.
j. Insert Earnings:
In cells C14-C23 insert each person's earnings. DO NOT put any dollar signs or commas in yet, because there is better easier way to do it which we will lean later!
k. Insert headings for Total and Average:
In cell A25 type: Total In cell A26 type: Average
Make them both BOLD!
l. Creating a formula to TOTAL up a column:
The first formula you will use will calculate the total earnings for a column of 10 numbers. Once this formula is in place it will automatically adjust the total if any individual salary numbers should change.
Let's get started.
Click in cell C25 and type the following: =sum(
Now drag/hi-light/select cells C14-C23. This will automatically place them after the parenthesis and tell the computer to do something to all of these numbers. Now just type a closing parenthesis → )
Your formula should look like this → =sum(C14:C23)
Once you hit ENTER your column should be totaled!
DO NOT worry about commas or $$$ yet, because you'll learn an easy way to do that later!
m. Creating a formula to AVERAGE a Column:
Just like in regular math, to create a formula that will average – you need to add up all of your numbers and then divide by how many numbers were involved.
There are several ways to do this with a formula.
Here is one way:
Click on cell C26. Follow all of the steps you used to get a column total (SEE step 11)
but add a SLASH ( / ) which stands for division and then add the number 10 after the slash because there are 10 numbers that we are adding and then averaging.
Your formula should look like this--> =sum(C14:C23)/10
*ANOTHER WAY to do this would be to type: =sum(
-Then click on cell C25 Which has the calculated total in it.
- Next type the slash key ( / ) which stands for division and the number 10 because that is how
many numbers we are averaging.
- Finally, type in the closing parenthesis. )
Your formula would look like this -> =sum(C25/10)
Either way works!
Hit ENTER and you should now have your AVERAGE calculated! :-)
n. Dollar signs and Commas done Automatically!
Follow these steps to insert dollar signs and commas. First hi-light/select by dragging the cells involved → C14-C26. Then, go way-up on the top MENU Bar and select--> FORMAT and then CELLS. This will open the Format Cells Window.
o. Be sure the Numbers tab is selected.
Under Category select Currency.
Under Format select the first choice that shows a dollar sign and in this case NO decimals. Under Options, leave the Decimal Places set to “0”.
Be sure the “Thousands Separator” is checked → that will insert your commas!
Select OK on the bottom.
You should now have dollar signs and commas!
p. Creating a formula to MULTIPLY:
In column D we need to create a formula that will multiply player Earnings by 5.
Open Office Calc uses the symbol * to multiply.
Click in cell D14 and start out by typing in our same basic formula--> Type; =sum(
Now click in cell C14 again (to select it)
To multiply by this cell by 5--> now type *5 and then type in a closing parenthesis )
Hit ENTER and you should have their salary multiplied by 5.
q. Copy / Paste to get remaining numbers:
Click on Cell D14. Go up to EDIT--> COPY.
Now hi-light/select cells D15 to D26 to select all of these cells.
Now Go up to EDIT--> PASTE.
Your Formula just multiplied all of the remaining salaries, totals and averages by 5!
r. Format Currency
Use what you've learned to add Dollar Signs and commas for D14 & D26.
Do not just type them in (see step 13 for help)
s. Make a whole row BOLD:
In the far left column, hi-light/select the numbers 25 & 26 and select the “B” for BOLD.
That should make the whole row bold!
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Congratulations! You have completed your first spreadsheet.
Parts 2 & 3 will all be done on this same document-- just a little further down!
GO TO: ACTIVITY 2! : TOP EARNING MUSICIANS