## Intro. to Spreadsheets: Top Ten Athletes

Assignment #1

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 Athletes**

**Spreadsheet 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.)

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

__and make it__

**Spreadsheet Introduction****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:

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**

**to do this would be to type: =sum(**

*ANOTHER WAY

*ANOTHER WAY

-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!

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!

Parts 2 & 3 will all be done on this same document-- just a little further down!

GO TO: ACTIVITY 2! :

__TOP EARNING MUSICIANS__