computer science homework2 guidlines
CS1100: Excel Lab 1
Filtering and Summarizing Data
To complete this assignment you must submit an electronic copy to Blackboard by the due date.
Use the data in the starter file. In this lab you are asked to filter and calculate various descriptive statistics on a data set about students at a university and calculate various descriptive statistics on a data set about loans and investments. In addition, you will solve a problem using induction.
Problem 1 (25 Points)
In the sheet “Problem 1” you are given a list of festivals, their locations and the seasons that they fall in.
Your task is:
1. Using IF, filter the data for these festivals by season. You will need to add a column for each season and then use the IF function to filter the data by season. Your formula should use proper anchoring so it is copyable both down and across.
2. Calculate the total number of festivals each season as well as the percentage of festivals that fall in each season.
Your solution must look like this (including the formatting):
Your solution must take into account the possibility that the data could change, i.e., do not “hard code” any data values. If the season of a festival changes, your calculations should update to the correct values automatically. Additionally, if new rows are inserted or existing rows are deleted, your spreadsheet model should still work.
Problem 2 (40 Points)
In the sheet “Problem 2”, you are given information about a savings account. Use this information to create a savings calculator. Notice that some cells have been given names. Use these names in formulas where appropriate. You can view all named cells in the Name Manager under the Formulas
CS1100 Lab E1
tab.
1. Use formulas to fill in the columns as shown below, including the column for DepNo. Lookup the
DATE formula to figure out how to fill in the Payment Date column. You may type in the Extra
Deposits as they appear in the figure below.
2. In cell I7, calculate the
Filtering and Summarizing Data
To complete this assignment you must submit an electronic copy to Blackboard by the due date.
Use the data in the starter file. In this lab you are asked to filter and calculate various descriptive statistics on a data set about students at a university and calculate various descriptive statistics on a data set about loans and investments. In addition, you will solve a problem using induction.
Problem 1 (25 Points)
In the sheet “Problem 1” you are given a list of festivals, their locations and the seasons that they fall in.
Your task is:
1. Using IF, filter the data for these festivals by season. You will need to add a column for each season and then use the IF function to filter the data by season. Your formula should use proper anchoring so it is copyable both down and across.
2. Calculate the total number of festivals each season as well as the percentage of festivals that fall in each season.
Your solution must look like this (including the formatting):
Your solution must take into account the possibility that the data could change, i.e., do not “hard code” any data values. If the season of a festival changes, your calculations should update to the correct values automatically. Additionally, if new rows are inserted or existing rows are deleted, your spreadsheet model should still work.
Problem 2 (40 Points)
In the sheet “Problem 2”, you are given information about a savings account. Use this information to create a savings calculator. Notice that some cells have been given names. Use these names in formulas where appropriate. You can view all named cells in the Name Manager under the Formulas
CS1100 Lab E1
tab.
1. Use formulas to fill in the columns as shown below, including the column for DepNo. Lookup the
DATE formula to figure out how to fill in the Payment Date column. You may type in the Extra
Deposits as they appear in the figure below.
2. In cell I7, calculate the