Working with Excel

Assignment Title >>    Working with Excel

Essential questions for this activity:

How can I use Excel to organize data and create results I can use?

End product for this activity:

You are going to create an Excel workbook that will include alphabetically sorted data, basic calculations designed to organize tabular data for an Internet shopping list, and an interest calculator.

Notes:

You will be using batches of numbers to work with various Excel functions.

We will stick with fairly basic Excel functions, but please know that you can go completely wild with Excel. It can forever dominate your destiny - think Star Wars.

We are going to start out by entering and sorting data.

Begin by following the instructions in Step 1. here

 >> Instructions

• Open the very basic excel sheet by clicking here.

  Open the first saved version of the Excel workbook by clicking here.
   

• Begin by looking at how the formulae in D3, B20, and I20 of the very basic Excel sheet are written. You can do this by clicking in them and looking at the formula bar near the top of the screen.
• What do you see about how they are written?
• What is in the cells to indicate certain functions?

Creating the class list

1. Create some imaginary data in four columns:
2. First, enter a series last names into a column at left of your screen. Make it about 15 names long.
3. Next create a series of first names in the next column to the right.
4. Then, create a column of imaginary quiz scores in the next column. Let's pretend they are quiz scores with a maximum value of 25 points.
5. We are going to sort the data and check the scores with all sorts of awesome little measures.
6. When you finish entering the imaginary data, save your project ast Excel Project One.
7. Now, sort the data by last name and keep the scores in the correct spots.
8. Next, perform a search for the mean, median, and mode in the scores you entered.

They are shown in the steps above.

Open the first saved version of the Excel workbook by clicking here.

Here is pie example one

Sample data for your interest calculator

Data stack

Electricity data

Check your work with another student.

 >> Instructions

Make a shopping list on the next spreadsheet in the workbook.

The data in groups

1. Create a six column group for data in Excel. The column titles will be Item Description, URL for Item, Quantity of Items, Price per Item, Shipping Costs, and Total for this Item.
2. Color the cells at the top of the sheet to indicate there label and use.
3. Insert a function to calculate the price for each group of items in the cell under Total for this Item.
4. Repeat that function for 20 or thirty cells in the series of columns you are creating.
5. Create a total cost cell at the bottom of your column group. This needs to calculate the total spent for all the items plus their shipping costs.
6. SAVE your workbook.
7. Go shopping! Log into at least three different shopping sites.
8. Find about 10 - 20 separate items, but your goal is to spend between $1000 and $1010 dollars. Any appropriate items are OK.
9. You need to purchase a minimum 3 of the same item at least once.
10. SAVE your sheet in your workbook.

Here are some handy tips for the data:

Keeping the data in similar groups that you can identify by appearance and location is handy.

Keeping your column and function descriptionsis also very handy.

I like to have my data sets close enough together that they can appear on one screen view.

>> Instructions
Create an interest calculator.

1. Create a six column worksheet in Excel. Label the columns, annual deposits, year, total deposits, total deposits with interest, and a multiplier column to enter the interest rates.
2. Follow the in-class instructions for writing the formulae for the the cells. Watch for the details on this step.
3. SAVE this page as part of your workbook.
4. Test your calculator by having changing some of the rates and deposit amounts. here

You will essentially complete this assignment in class. It should resemble the example printed in class, but have the data assigned to your last name in it.

Evaluation Form

In your Excel Workbook, keep all of the following as saved pages:

1. Save your class list with the data sort, mean, median, and mode functions completed.
   15 points - all are complete
   12 points - all but one are complete
   9 points - all but two are complete
   6 points - only one is complete
    
2. Save your shopping list with the graph, sales tax calculator, and totals completed.
   15 points - all are complete
   11 points - all but one are complete
   8 points - all but two are complete
    
3. Save your interest planner with the deposit amounts, years, and total calculations completed.
   15 points - all are complete
   11 points - all but one are complete
   8 points - all but two are complete

Assignment Total - 45 points