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
• Understand Order of Operations/Associativity
  • what are the mathematical operators in Excel?
• Relative vs Absolute Cell Reference
• Cell Formatting

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.

Tricky: 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.

	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 to calculate e^p

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.
In another column, 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 two columns of data!

Use conditional formatting to highlight the Fibonacci numbers that are even in red.

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 - If, Chart

Go to this web page: http://www.wunderground.com/history/airport/KHIO/2013/9/1/MonthlyHistory.html?req_city=NA&req_state=NA&req_statename=NA
and Download the Comma Delimited File at the very bottom of the page (Right Click and Save As.  Name the file MonthlyHistory.csv). This file contains the Daily Observations table.

Open this file with Excel.  Save the file as a new Excel Workbook.

Clean up the data if you need to.  Add a column named "Comfort".  When the high temperature is 65 or
over display "warm".  When the high temperature is less than 64 but over 49 display "nice".  When the high
temperature is under 50 display "cold".

Use conditional formatting to highlight all cells in the Events column containing the word Rain with a blue background.

Add a column named "Enjoyability".  When the high temperature is over 60 and the high humidity is less than 80, display "Wonderful".
When the high temperature is over 60 and the high humidity is 80 or more, display "Sticky".  When the high temperature is 60 or
less display "Oregon".

Add a column "Ground".  When the high dew point is greater than the low temperature, display "Wet", otherwise display "Dry."

Build a line chart that shows the high, average, and low temperature each day.

Build a line chart that shows the high temperature, high dew point, high humidity, and high sea level pressure each day.

Tricky: Highlight, in yellow, cells in Precip. that have a value greater than zero but where Rain does not show up in the Events column.