I'm having trouble figuring something out in Excel and I need it for my project. The goal is to automate a table look-up so that the user doesn't have to dig out the table and look up the values themselves.
(http://i37.photobucket.com/albums/e70/erkishhorde/PSWadjustment.png)
It needs to be able to find the value on this table, given two inputs: max opening size and percent full height sheathing. Neither number will necessarily exactly match the values show so the spreadsheet needs to be able to figure out which column is closest to the value given and then use those values to pull the correct value from the table.
For example: I have a wall that's 8' high and has a 5' tall window in it. That means that I need to use the 2h/3 column. Then let's say that I have 55% full height sheathing. Instead of interpolating I want the program to use the 60% sheathing row. End result I want is for it to kick out the factor 0.71
Any help is greatly appreciated.
I could probably write a formula, but don't have time. Here are the formulas I would start with though...
1 =roundup() and or rounddown() to get to the closest value entered
2 =lookup() to then search an array to match
mitt
I am with Mitt.
I could easily whip you out a sheet that would suit your needs, but I don't have time right now.
Also, it looks like you embedded a picture and I can't see it at work.
effing filters.
Took me an hour or so to dig around and find the info I needed.
I ended up using this twice to get the x,y values
=INDEX(array,MATCH(MIN(IF(array-E105>=0,array,FALSE)),IF(array-E105>=0,array,FALSE),0))
and then took the x,y values and used and index w/ match to pull the value from the table
=INDEX(DataRange, MATCH(y,yrange,0), MATCH(x,xrange,0))
Thanks, guys.
cool - I will have to check out index - I have never used it.
mitt