What should you expect on the midterm?

• Two or Three spreadsheets to build
• Simple calculations (=4+A1)
• Functions (AVERAGE, PMT, ...)
• Build a table (write a formula and drag it to fill the table)
• Build a Line Chart (properly labeled!)
• Build a Pie Chart
  
• Conditional Formatting
• IF()/OR()/AND()
• Goal Seek/What If Analysis
  • Placing comments in cells
• Named Cells
• Regression/Trendlines
  
• Understand Order of Operations/Associativity
  • what are the mathematical operators in Excel?
• Relative vs Absolute Cell Reference
• Cell Formatting
• Import data from the web
• Connect to Turing
  

Note: problems marked tricky below would be a bonus question on the exam.

Problem 1 - Formulas

Write an Excel worksheet that will produce the sum and average of the numbers in column A below.
Place these values in B2 and B4, respectively.

 In each cell of column C, produce the sum() of the values in column A that are in that row or higher. For example, in C1 find the sum of A1, in C2 find the sum of A1 to A2, in C3 find the sum of A1 to A3. Do this using a formula that you can write in C1 and drag down to C5.  Hint: Write the formula for C1 and for C5. How are these two formulas the same? Different?

	A	B	C
1	9	Sum
2	1
3	2	Average
4	3
5	4

Problem 2 - Loans

Build a worksheet that will allow the user to input the yearly interest rate, number of years, and total value for a loan that has monthly payments.  Use an Excel function to determine the monthly payment.

For the inputs, use 7.8% yearly interest, 30 years, $300,000 dollars.  Determine, over the life of the loan, how much interest is paid.

Use goal seek to determine how large a loan you can take out if you can afford a $3,500 monthly payment and the interest rate remains at 7.8% for 30 years.  Be sure to format your data correctly and use named cells where appropriate.

Problem 3 - If

Using the following data, determine the average height of an oak tree and for each tree print a message ("Above average", "Average", "Below Average") to denote where each tree is with respect to the average height.  Build a Line Chart to show the height of each Tree.

	A	B	C
1	Tree ID	Height	Above/Average/Below
2	Tree1	100
3	Tree2	75
4	Tree3	30
5	Tree4	23

Problem 4 - Table, formula, functions

A certain type of bacteria increases based on the following model: B(t) = B(0) + 100e^0.2197t where t is time in hours and B(0) is the starting population of bacteria.  Check out the exp() function in Excel

Using Goal Seek, at what time can we expect there to be 1,000,000 bacteria when the starting population is 10. Give your answer to two decimal places.

Build a table with the columns, t, B(t) to show the growth of the bacteria until 1,000,000 bacteria exist.  Draw a Line Chart to show this growth.  Be sure to properly label your chart and data.

Problem 5 - Table, formula, conditional formatting

The first few fibonacci numbers are: 1,1,2,3,5,8,... where the first two numbers are always 1,1 and each subsequent number is found by adding the previous two. In one column, I would like you to find the first 20 fibonacci numbers.

Tricky: Use conditional formatting to highlight the Fibonacci numbers that are even in red. (hint: find a function that will tell if you if a number is even).

Problem 6 - Retirement savings. Chart
After you graduate and get a job, you want to save enough money every year to have $1,000,000 when you retire.  How much money would you need to save every month to have $1,000,000 after 45 years if you invest your money in an account that earns 5.5% yearly interest?

How much would you need to invest every month if you only get 4.5% yearly interest?

Build a graph that shows the balance in the retirement account after each month.

Problem 7 - Table, Formulas

Congratulations! You saved up $1,000,000 by the time you retire!  If, every year after you retire, you take $50,000 out of the account to live on, and earn 3% yearly interest on the money remaining, how many years can you go before the account is empty of money? (Note, take the money out of the account and then calculate the interest earned).

Build a nicely formatted table to solve this problem.

Problem 8 - Regression

Copy the Excel workbook USPop.xlsx from CS 130 Public folder on Turing.  This file contains US Population estimates for the time period April 2000 to May 2010 (Month 0 is April 2000).  At a column, in column C, that shows the growth from each month to the next.  For example, cell C3 should show the increase in population from April 2000 to May 2000.   

Build a graph to show the Estimated Population over time.  Use a secondary axis to graph Growth over time.  Add a linear trend line (regression) for Population and a linear trendline for Growth.  Be sure to display the equation and R-squared value for each. 

What population is expected in Month 122?  What Growth is expected in Month 122?  In which Month is the population expected to reach 320,000,000?

Problem 9 - Table, formula, functions

I would like you to find the first 20 solutions to the equation: y=x^2 over the interval 1<=x<=20 where x is an integer. Once you have these two columns laid out, show the graph of each set of numbers with all axes properly labeled and a chart title. 

Tricky: Solve this problem with only one column of data!