Reference no: EM133733906
Assignment: Computer Science Preparing a Spreadsheet Containing Data Project
Preparing a Spreadsheet
Begin the planning of a new spreadsheet that contains two unique data sets that can be found online and are recent (i.e., the past six months). These two data sets should be on different topics. For example, sports scores and then the cost of fuel. Place each dataset onto its own worksheet and rename the sheets to represent the content of each sheet. Example datasets are:
1. The temperature of your city for the past 5-10 days
2. Scored points per game of your favorite sports team for their past 5-10 games
3. The amount you paid for gas for the past few months
4. Dow Jones or similar financial market
5. Most anything else you can tabulate or can track. Be creative, but be sure to provide the link.
Before starting, plan how the data is to be structured. Ask yourself these questions:
1. What is the purpose of the spreadsheet? e.g., a budget, tracking sales, analyzing numbers, creating charts for presentations, etc.
2. Determine the type of data you are working with. Is it currency, percentage, or larger number needing a comma or decimals?
3. How many decimal places are needed? Be consistent.
4. How is the information to be organized? By time? (day, month, or quarter?) By category? (income, expenses, etc.)
5. What text labels are needed?
6. What text and cell formatting should be used? (bold, indents, underline, font color, cell COLOR shading (a little is good, but not too much), etc.)
7. What data needs to be summarized?
8. Will formulas or functions be used to derive this information? e.g., total (sum), average, maximum/minimum value, payment, etc.
9. Is a chart needed to enable the visualization of trends?
Spreadsheet Functions Part I
Access the file you posted in the last module and Save As the same file name but add 'Part 2' to the end of the file name.
1. Use at least two different functions (e.g. SUM, MAX, AVERAGE, MIN, COUNT, etc.) to summarize each data set created in the last module.
a. Use cell ranges with these and do not use the same functions on both sheets.
b. Use yellow cell highlighting to signify the location of the functions.
c. Ensure any decimals depict meaningful values. i.e., are limited to one or perhaps two decimal places. To illustrate, noting the average temperature was 71.23456 degrees is not meaningful to most people. Write this as 71 degrees or perhaps 71.2 degrees.
2. Next, create an IF statement that evaluates at least one cell on each sheet.
a. For example, you might create a function to evaluate the results of the SUM function. It might read something like: IF the cell > 100 then 'True' else 'False'. Or perhaps you might compare scores in a game to have the text 'Win' or 'Loss' appear based on the cell values evaluated.
b. Be sure to add a label next to the cell that contains the IF statement.
c. Feel free to be creative but be sure to use cell references here.
d. As needed, reference the tutorials and the help areas within Excel. Knowing how to access Help is an important skill.
Spreadsheet Functions Part II
On a new sheet of the same file, create a worksheet that contains data and a calculation determining monthly payments for a proposed purchase. Include the following:
1. Identify your dream vehicle (plane, automobile, boat, etc.) and estimate its cost and the annual interest rate if you financed its purchase.
2. Enter text labels in Column A for Loan Amount, Interest Rate (APR), Term in Years, Payment, etc., onto the spreadsheet.
3. Enter the numbers for each item in Column B, except the monthly payment of the course. We'll use a function to determine this.
4. Apply appropriate number formatting (currency, percentage) where appropriate.
5. To calculate the monthly payments, use the PMT function with cell references. Ensure you are converting the term and interest per the tutorial. Do so within the function.