CCE 170 - Final Exam KEY

Fall Semester 2021

31.  (9.2.1) The following spreadsheet is designed to summarize the results of an estimation algorithm:

Each column in the table represents a different test case for an estimator. In each case, the estimator made 20 predictions. The actual value (i.e., the "answer") is shown in the target row. When analyzing the performance of an estimator, the following two equations can be used to compute an error summary:

where

n = number of estimates

e_i = individual estimate

t = target value

mse = mean square error

mae = mean absolute error

Click here to download a copy of the spreadsheet. Then answer the following questions:

Click here to download a copy of the solution.

a. (7 pts) (9.2.4) Write a custom function in VB called mse for computing the mean square error as shown above. Your function should take two arguments as input: a range of cells (r) containing the list of estimates, and a target value (t). It should then return the mean square error value. You should not pass anything else to your function nor should your function read directly from cells. To get the number of items in the range, you should count the items as you loop through the range. Do not reference any built-in Excel functions from your VB code.

b. (1 pt) (9.2.1) Enter a formula in cells C28:H28 for computing the mse using the function you wrotefor each of the test cases (columns).

c. (5 pts) (9.2.4) Write a custom function in VB called mae for computing the mean average error. Your function should be structured similarly to the mse function but it should return the mean absolute error.

d. (1 pt) (9.2.1) Enter a formula in cells C29:H29 for computing the mae using the function you wrote for each of the test cases (columns).

(Upload instructions and links went here)