CS 130 Midterm Review

Spring 2011

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!)
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

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

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

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

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.

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

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 12 fibonacci numbers.
In another column, I would like you to find the first 12 solutions to the equation: y=x^2 over the interval 1<=x<=12
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.