CS 130
Midterm Review
Winter

What should you expect on the midterm?

Sample Written Questions:

1. Describe the difference between $A$1, $A1, A$1, and A1. Explain why this difference is important as you drag formulas around the worksheet.

2. If you want to display a decimal number (3.1415) as a whole number (0 digits after the decimal point) you could format the cell, use the INT() function, or use the ROUND() function. How do these methods differ? How will each method affect a cell that references that cell?

3. List the Excel comparison operators and what they mean.

4. What is the solution to: =MAX(4,3)

5. What is the solution to: =4+2/4-1. Write your answer with two places after the decimal point. You will receive zero credit for answers containing a fraction.

6. When using the PMT() function to determine the value of the monthly payments to pay off a loan, why do you need to specify the rate as 6%/12 if the loan charges you 6% yearly interest? For example: =PMT(6%/12, 12*5, 5000) will calculate the monthly payments to pay off a 5 year loan of $5000 with a 6% yearly interest rate.

7. How would the arguments to PMT() change if, in the above example, payments were made quarterly (every 3 months)?

8. Why is the value returned by the above PMT() example negative?

9. TRUE FALSE   A Named Cell is a relative reference.

Problem 0 - Formulas


Given the following spreadsheet:


 A B C D
1 -2 =$A1+A$2 =sum(A1:A3) 9
2 2 9

3 4 3
=D1-C1

What formula will appear in C2 if the formula from B1 is copied to C2?
What value will appear in cell C2?

What formula will appear in D2 if the formula from B1 is copied to D2?
What value will appear in cell D2?

What formula will appear in C3 if the formula from D3 is copied to C3?
What value will appear in cell C3?

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) + 100e0.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 ep

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.

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 - You are the Bank!

You are a Bank!  You provide loans with various interest rates.

You loan Pat $10,000 for 3 years with 3.4% annual interest.  What is Pat's monthly payment (since you are receiving the payment, this payment should be positive).

You loan Chris $15,000 for 3 years with 2.3% annual interest.  What is Chris's monthly payment (since you are receiving the payment, this payment should be positive).

Which loan will pay the bank more money in interest?  Write a formula (or set of formulas) that will display either PAT or CHRIS to show which person will pay more in interest over the life of their loan.

Problem 8 - 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.

Tricky: Use PMT and goal seek to solve the same problem (find the number of years before your account balance reaches zero)!

Problem 9 - If, Chart

Import the Daily Observations Table from this web page:

 http://www.wunderground.com/history/airport/KHIO/2012/4/12/MonthlyHistory.html

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.