Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index(match) Wind Uplift Calculations (match four conditions)
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index(match) Wind Uplift Calculations (match four conditions)
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index(match) Wind Uplift Calculations (match four conditions)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with INDEX MATCH formule depending on 2 conditions | Excel Worksheet Functions | |||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |