Find a Value by interpolation from a table of x and y values

B

BuickGN87

Here's what I have:(of course each of the four times goes with the Vol.
beneath it)
Time: 5 10 15 30
Vol.:7.50 5.98 5.05 3.74
I need to provide a time value (ex. 12) and have the program interpolate to
return the EXACT value from the volume data. The nearest value won't due.
Any help would be appreciated. Thanks
 
D

Dave F

Can you use the Solver tool? Tools--Solver.

If you don't have that option available, then you need to install the
Analysis Toolpak, via Tools--Addins.

Dave
 
B

BuickGN87

Dave, Thanks for the tip, but further assistance is needed. I loaded the
solver add-in, but I am having trouble with the formulas. I need this to
find a rainfall amount for a given year of storm frequency at a user chosen
duration. Example: Given the 25 Yr storm frequency, find the rainfall amount
for a duration of 12 minutes. Here is more on what I am doing. I need to
have the program interpolate (in the example above between the 10 & 15 minute
numbers for the 25Yr storm (5.99 & 5.06) and return the correct number of
5.62. Straight line interpolation is ok.

Duration (Minutes)
5 10 15 30 60 120
1Yr 4.21 3.36 2.80 1.92 1.20 0.74
2Yr 5.04 4.03 3.38 2.33 1.46 0.90
5Yr 5.98 4.79 4.04 2.87 1.84 1.14
10Yr 6.66 5.32 4.49 3.25 2.12 1.32
25Yr 7.51 5.99 5.06 3.75 2.50 1.58
50Yr 8.14 6.48 5.47 4.12 2.79 1.79
100Yr 8.77 6.97 5.87 4.50 3.10 2.00

Any further ideas?
Thanks
 
V

vezerid

Without the Solver you can find the trendline using the charting
utility. For example, for the 25Yr, select the first row (duration) and
the 25Yr row and create a XY-scatter chart.

Then right-click on the data series and choose Trendline... In the Type
tab choose polynomial (degree 2) or exponential. In the Options tab
check: Display equation on chart AND Display R-squared.

I would not go for the straight line method. Your data is certainly
nonlinear. I don;t know the statistical theory behind this problem, so
I don't know which function best models it.

HTH
Kostis Vezerides
 
Top