Excel has many functions, that can do most of the calculationsyou'll want to use. However, there's one very useful and importantfunction lacking: simple linear interpolation.

The easiest way to do interpolation with cell formulas is:create two helper columns to the right with the SLOPE and INTERCEPTbetween each pair of points. Use LOOKUP, VLOOKUP, or MATCH functionto lookup the slope and intercept, and multiply it out(y=mx+b).

So, the assignment is three parts:

1) interpolate from a range of x and y values using built-inexcel functions

2) write a user-defined function in VBA to interpolate foryou

3) write a Subroutine, which interpolatesany missing values in the selected column(s).

Examples:

For parts one and two, use the following example:

Folsom Lake storage cannot be measured directly, the elevationis measured, which is used to calculate storage. Theelevation-storage curve is given from USGS (Links to anexternal site.), as follows:

Elev (ft)	Vol (AF)
345	123,600
350	137,900
360	170,600
370	210,500
380	258,600
390	314,100
400	376,900
420	525,500
440	703,800
460	908,400
479	1,125,000

If you wanted to know the storage at 425 feet, you need tointerpolate between the given values. Using the SLOPE and INTERCEPTfunction we would get y=8915*425-3218800, which equals 570,075 AF.Your Excel formula and VBA formula should lookup the appropriatevalue, so if I change the input of 425 to 465, your answer shouldchange.

~~~~~

For part 3, the subroutine should first check if the Selectionis actually a Range. If it is, the subroutine should go down eachcolumn until a blank value is found. Once a blank is found, itneeds to be filled in with a 'best guess' interpolated from validvalues above and below. There might be sequential blanks, so the.End(xlDown) and .End(xlUp) methods of a Range will be useful here.The values it fills in depend on the number of sequential blanksand the values above and below. For example:

3

7

If this range was selected the code would fill in a 5.

3	2
	2
7	0

If this range was selected, the first column would fill in 4,5,6and the second column would fill in 1.333 and 0.667.

And if a much bigger range was selected with many more blanks,it would work the same.

Answer rating: 100% (QA)

For the first part of the assignment you can use Excels builtin functions to perform linear interpol... View the full answer