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

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.

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

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.

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

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" wrote:

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.

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

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,Speed s,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)*(D1:D100=EffectiveWindArea)*(ROW(B1 :B100)),MATCH(WindSpeed,E1:G1,FALSE))

Like

=INDEX(E1:G100,SUMPRODUCT((B1:B100=7)*(C1:C100=2)* (D1:D100=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" wrote in message
...
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" wrote:

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.

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

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" wrote:

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,Speed s,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)*(D1:D100=EffectiveWindArea)*(ROW(B1 :B100)),MATCH(WindSpeed,E1:G1,FALSE))

Like

=INDEX(E1:G100,SUMPRODUCT((B1:B100=7)*(C1:C100=2)* (D1:D100=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" wrote in message
...
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" wrote:

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.

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

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" wrote in message
...
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" wrote:

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,Speed s,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)*(D1:D100=EffectiveWindArea)*(ROW(B1 :B100)),MATCH(WindSpeed,E1:G1,FALSE))

Like

=INDEX(E1:G100,SUMPRODUCT((B1:B100=7)*(C1:C100=2)* (D1:D100=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" wrote in message
...
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" wrote:

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.

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

Bernie,
My address is jim-m e i e .

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" wrote:

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" wrote in message
...
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" wrote:

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,Speed s,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)*(D1:D100=EffectiveWindArea)*(ROW(B1 :B100)),MATCH(WindSpeed,E1:G1,FALSE))

Like

=INDEX(E1:G100,SUMPRODUCT((B1:B100=7)*(C1:C100=2)* (D1:D100=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" wrote in message
...
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" wrote:

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.

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

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" wrote in message
...
Bernie,
My address is jim-m e i e .

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" wrote:

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" wrote in message
...
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" wrote:

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,Speed s,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)*(D1:D100=EffectiveWindArea)*(ROW(B1 :B100)),MATCH(WindSpeed,E1:G1,FALSE))

Like

=INDEX(E1:G100,SUMPRODUCT((B1:B100=7)*(C1:C100=2)* (D1:D100=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" wrote in message
...
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" wrote:

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.

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

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

"Bernie Deitrick" wrote:

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" wrote in message
...
Bernie,
My address is jim-m e i e .

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" wrote:

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" wrote in message
...
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" wrote:

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,Speed s,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)*(D1:D100=EffectiveWindArea)*(ROW(B1 :B100)),MATCH(WindSpeed,E1:G1,FALSE))

Like

=INDEX(E1:G100,SUMPRODUCT((B1:B100=7)*(C1:C100=2)* (D1:D100=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" wrote in message
...
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" wrote:

J,

Perhaps,

=SUMPRODUCT((RA=RoofAngle)*(RZ=RoofZone)*(EWA=Effe ctiveWindArea)*(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" wrote in message
...
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.