Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a Value by interpolation from a table of x and y values
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a Value by interpolation from a table of x and y values
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 -- Brevity is the soul of wit. "BuickGN87" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a Value by interpolation from a table of x and y values
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 "Dave F" wrote: 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 -- Brevity is the soul of wit. "BuickGN87" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a Value by interpolation from a table of x and y values
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 BuickGN87 wrote: 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 "Dave F" wrote: 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 -- Brevity is the soul of wit. "BuickGN87" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing two columns of data to find common values | Excel Discussion (Misc queries) | |||
Find multiple repeated values in a Pivot Table | Excel Worksheet Functions | |||
Populating a table based on values in another table | Excel Worksheet Functions | |||
How do I have a data table display cell names rather than values? | Excel Worksheet Functions | |||
find instances & report neighboring values | Excel Discussion (Misc queries) |