CS 130 Midterm Review


Don't forget the first day of class! What is Computer Science? What is the research process?

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 pie chart to show the
number of trees in each category.


 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) + 100e0.2197t where t is time in hours
and B(0) is the starting population of bateria.

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 bateria until 1,000,000 bateria 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.

Problem 6

The table below contains data on tree height, age, and average rainfall in that tree's location.  Perform a linear regression
on the tree age to see how well that predicts tree height.  Perform a separate linear regression to determine how well
average rainfall predicts tree height.  Which value better predicts tree height?


 A
 B
 C
 D
1
 TreeID
 Height
 Age
 Avg. Rainfall
2
 1
 1
 1
 0.5
3 2
 6
 2
 4
4
 3
 14
 10
 5
5
 4
 15
 10
 5
6
 5
 22
 20
 9
7
 6
 31
 30
 13
8
 7
 65
 80
 19
9
 8
 80
 90
 19
10
 9
 70
 100
 5