Filtering and Interpolating

  • Thread starter Thread starter svbmbs169
  • Start date Start date
S

svbmbs169

How do I reference information in a drop down menu to a row of data?
And then once that row is referenced, how do I interpolate between tw
data points. For example, I want to choose a model number from a dro
down menu to reference a row of box volumes associated with the mode
number. From there say I have volumes of 10 20 30 etc for one mode
which correspond to lengths of 4 5 6 etc, respectively, referenced i
the row above. If I input this model number in one cell and a volum
of 13, I want another cell to output a corresponding length (whic
should be between 4 and 5). Is this possible? Thanks, Bill
 
Hi svbmbs169,

Try the following example:
1. Enter the volume values in cells D1:F1 and the length values in the
cells below, which would be D2:F2
2. Select cell A1 and click Data menu > Validation, under Settings tab
select List and type =$D$1:$F$1 in the Source box. Then uncheck the
option under the Error Alert tab and click OK
3. Enter the following formula in cell A2
=IF(ISERROR(HLOOKUP(A1,D1:F2,2,FALSE)),TREND(D2:F2,D1:F1,A1),HLOOKUP(A1,D1:F2,2,FALSE))

Now when you select numbers from the drop-down list in A1 or if you
just type a number there you should see the result that you expect in
A2. The formula in A2 will lookup the value that is in A1 in the table
D1:F2 and will give the corresponding number from the length row and
if cannot find it there will use the TREND function to interpolate it.

Alternatively, you can simplify the formula by just using the Trend
function if the result meets your needs. Type the following formula in
A4
=TREND(D2:F2,D1:F1,A1)

If the results from the Trend function are not accurate enough,
consider modifying the formula for polynomial curve. You can find that
in Excel Help for the Trend function: You can use TREND for polynomial
curve fitting by regressing against the same variable raised to
different powers. For example, suppose column A contains y-values and
column B contains x-values. You can enter x^2 in column C, x^3 in
column D, and so on, and then regress columns B through D against
column A.

Cheers,
Peter
 
Thanks Peter for your help. That gets me started, but my project is
little more complex. I thought if I could do what I asked before, I'
be set, but I'm in over my head. So I'd like to attach the file I'
working on to see if maybe you or anyone else can help me.

As you can see in the attached spreadsheet, I want a user to be able t
enter the coil and furnace models from a drop down menu and specify th
desired airflow. From their, I want the spreadsheet to output th
interpolated external static pressure for each available speed ta
based on the furnace model and airflow. Also, I want the spreadshee
to output the pressure drop across the coil based on the coil model an
the airflow. If the airflow is outside the range available for th
coil and furnace models, the spreadsheet should return some sort o
error.

Thanks for your help so far. Hopefully you can gEt me on my way wit
this.

Bill

Attachment filename: billy1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=58954
 
Back
Top