index(match) Wind Uplift Calculations (match four conditions)

[JMeier]

INDEX/MATCH works good on tables with two or three arguments but I haven't
been able to figure out how to use it with four arguments

Criteria
Roof Angle Roof Zone Wind Area (Sf)Wind Speed Wind Uplift
7 1 10 120 ?
7 2 10 120 ?
7 3 10 120 ?

Net Design Wind Pressure, Pnet30 (psf) (Exposure B at h = 30 ft. with I =
1.0 and Kzt = 1.0)
Basic Wind Speed V (mph)
ID Roof Angle Roof Zone Wind Area 85 90 100
1 7 1 10 -13.0 -14.6 -18.0
2 7 1 20 -12.7 -14.2 -17.5
3 7 1 50 -12.2 -13.7 -16.9
4 7 1 100 -11.9 -13.3 -16.5
5 7 2 10 -21.8 -24.4 -30.2
6 7 2 20 -19.5 -21.8 -27.0
7 7 2 50 -16.4 -18.4 -22.7
8 7 2 100 -14.1 -15.8 -19.5
9 7 3 10 -32.8 -36.8 -45.4

I can get the Roof Angle, Roof Zone, Effective Wind Area, and Wind speed
from other work sheets. I get the "Too Many Arguments" message when trying to
get the negative uplift numbers.

 

[Bernie Deitrick]

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=EffectiveWindArea)*(WS=WindSpeed)*UL)

Where

RA,RZ, EWA, WS, UL are the ranges in your table (of the same size), and
RoofAngle, RoofZone, EffectiveWindArea, WindSpeed are your values
As long as you have exact values that match the values in the table.

HTH,
Bernie
MS Excel MVP

 

[JMeier]

Bernie,
I'll give it a try and let you know how it works. It will be nice not to
have to drag a bunch of books around the country.

Thanks,
Jim Meier

 

[Bernie Deitrick]

Jim,

I just noticed that your table is a cross tab table and not a database. In
that case, you will need to use

=INDEX(UpliftValues,SUMPRODUCT(...),MATCH(WS,Speeds,FALSE))

The best way is to use row 1 as your basis: with roof angles in B, roof
Zones in C, Wind area in D, and windspeeds in E1:G1

=INDEX(E1:G100,SUMPRODUCT((B1:B100=RoofAngle)*(C1:C100=RoofZone)*(D1100=EffectiveWindArea)*(ROW(B1:B100)),MATCH(WindSpeed,E1:G1,FALSE))

Like

=INDEX(E1:G100,SUMPRODUCT((B1:B100=7)*(C1:C100=2)*(D1100=10)*ROW(B1:B100)),MATCH(90,E1:G1,FALSE))

Make the 100s as high as you need....

Sorry about that.

HTH,
Bernie
MS Excel MVP

 

[JMeier]

Bernie,
I set up the table as a list with a criteria and extraction area, but I
couldn't figure out how to get the data without going to the worksheet. The
table works as follows:
The first row is a given roof with a slope(deg) <=7; The roof zone is Zone 1
(the interior portion of the roof); The effective wind area is the amount of
roof area that is calculated for uplift pressure (p.s.i.) in this case 10 Sf;
The -13.0 is the uplift pressure in pounds per square foot at a wind speed of
85 mph. There are twelve columns from 85 to 170 mph wind speed and thirty-six
rows.

There isn't any calculation required inside the table. The numbers in the
table cannot be changed.

Each row would require the roof angle, roof zone, and effective wind area to
all be "true". A match of the correct wind speed column and the required
result of the uplift located at the intersection of the row/column.

Sorry I didn't supply more information in the original post. I know it's
possible. If you can work the problem on paper, you can work the problem
faster with Excel.

Thanks,
Jim Meier

 

[Bernie Deitrick]

Jim,

I have set up a workbook with the table that you posted, with a working
formula to extract the uplift.

Post with your email address (add spaces etc to protect it) here or reply to
me - take out the spaces, and change the dot to . - and I will send you
the workbook privately.

Bernie

 

[JMeier]

Bernie,
My address is jim-m e i e (e-mail address removed).

Should I include the headings when I set up the ranges, or just the
numerical data? Also, can the numbers in the wind speed Headings be formatted
numerical or should they remain text?

Thanks,
Jim

 

[Bernie Deitrick]

Jim,

I sent the file to
jim dot meier at denardandmoore dot com

Let me know if you do not receive it.

HTH,
Bernie
MS Excel MVP

 

[JMeier]

Bernie,
It works! Thank you for your help!
Jim

Jim,

I sent the file to
jim dot meier at denardandmoore dot com

Let me know if you do not receive it.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Great! Glad to hear it - and you're quite welcome.

Bernie
MS Excel MVP