Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex 2 table formula
Here are my two tables:
Table 1 Column 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Row 1 Slope Bands (%) R1 RS RE9 RE11 RE15 RE20 Row 2 0 14 0.5 0.45 0.4 0.4 0.35 0.35 Row 3 15 29 0.45 0.4 0.35 0.35 0.3 0.3 Row 4 30 44 0.4 0.35 0.3 0.3 0.25 0.25 Row 5 45 59 0.35 0.3 0.25 0.25 0.2 0.2 Row 6 60 99 0.3 0.25 0.2 0.2 0.15 0.15 Row 7 100 + 0 0 0 0 0 0 Table 2 Col 1 Col 2 Col 3 Row 1 ZONE Area Slope Row 2 R1-1 31.71 <15% Row 3 R1-1 32.72 15-30% Row 4 R1-1 63.70 30-45% Row 5 R1-1 17.29 45-60% Row 6 RE9-1 6474.83 45-60% Row 7 RE9-1 19602.34 15-30% Row 8 RE9-1 4438.14 <15% What I need to be able to do is to create a formula that multiplies the value in table 1 in columns 2-7 that corresponds to the slope and the zone with the lot area in table 2. For instance, I need to find a formula that will choose to multiply the value in table 1 Row 5, Col 4 with table 2 row, 6, column 3. I want it to know which value to pull from table 1 to multiply with in table 2 so the zone and slope categories match up....Is this possible? What I have done as a workaround is to sort the data by the zone and then apply formulas that I developed for each zone. i.e. =IF(slope="<15%", area*$Q$8,IF(slope="15-30%",Area*$Q$9,IF(slope="30-45%",Area*$Q$11,IF(slope="45-60%",Area*$Q$12,IF(slope="60-100%",Area*$Q$13,IF(slope="100%",Area*$Q$14)))))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex 2 table formula
I assume that your data is look like this¦
Table1 in Sheet1 from A1:G7 Table2 in sheet 2 from A1:C8 In TABLE 1 (Sheet1) Paste this formula in H2 cell =IF(ISERROR(VALUE(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE( A2,"","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"","-"),"+","")))-1))),VALUE(SUBSTITUTE(SUBSTITUTE(A2,"","-"),"+","")),VALUE(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE( A2,"","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"","-"),"+","")))-1))) Copy the H2 cell and paste it to the remaining cells of H Column depends on the A Column Data. Paste this formula in I2 cell =IF(ISERROR(VALUE(RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE (A2,"","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"","-"),"+","")))-1))),10000000,VALUE(RIGHT(TRIM(SUBSTITUTE(SUBSTITU TE(A2,"","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"","-"),"+","")))-1))) Copy the I2 cell and paste it to the remaining cells of I Column depends on the A Column Data. In TABLE 2 (Sheet2) Paste this formula in D2 cell =IF(ISERROR(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(C2,"<" ,""),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1))),0,VALUE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(C2,"< ",""),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1)))) Copy the D2 cell and paste it to the remaining cells of D Column depends on the C Column Data. Paste this formula in E2 cell =IF(ISERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C2,"< ",""),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1))),VALUE(SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%","" )),VALUE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C2,"<"," "),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1)))) Copy the E2 cell and paste it to the remaining cells of E Column depends on the C Column Data. In F2 cell Paste the below formula (This will bring you the result you have expected) =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A2,FIND("-",$A2)-1),Sheet1!$B$1:$G$1)))*(Sheet1!$H$2:$H$7=Sheet2!$ D2)*(Sheet1!$I$2:$I$7<=Sheet2!$E2),(Sheet2!$B2*She et1!$B$2:$G$7)) Copy the F2 cell and paste it to the remaining cells of F Column depends on the Table2 Data (Sheet2). In the above formula change the cell reference $H$2:$H$7, $I$2:$I$7 & $B$2:$G$7 to your desired range like this depends on your data on that column $H$2:$H$100, $I$2:$I$100 & $B$2:$G$100 like the below:- =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A2,FIND("-",$A2)-1),Sheet1!$B$1:$G$1)))*(Sheet1!$H$2:$H$100=Sheet2 !$D2)*(Sheet1!$I$2:$I$100<=Sheet2!$E2),(Sheet2!$B2 *Sheet1!$B$2:$G$100)) Apply it for the remaining cells of F Column. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "JD" wrote: Here are my two tables: Table 1 Column 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Row 1 Slope Bands (%) R1 RS RE9 RE11 RE15 RE20 Row 2 0 14 0.5 0.45 0.4 0.4 0.35 0.35 Row 3 15 29 0.45 0.4 0.35 0.35 0.3 0.3 Row 4 30 44 0.4 0.35 0.3 0.3 0.25 0.25 Row 5 45 59 0.35 0.3 0.25 0.25 0.2 0.2 Row 6 60 99 0.3 0.25 0.2 0.2 0.15 0.15 Row 7 100 + 0 0 0 0 0 0 Table 2 Col 1 Col 2 Col 3 Row 1 ZONE Area Slope Row 2 R1-1 31.71 <15% Row 3 R1-1 32.72 15-30% Row 4 R1-1 63.70 30-45% Row 5 R1-1 17.29 45-60% Row 6 RE9-1 6474.83 45-60% Row 7 RE9-1 19602.34 15-30% Row 8 RE9-1 4438.14 <15% What I need to be able to do is to create a formula that multiplies the value in table 1 in columns 2-7 that corresponds to the slope and the zone with the lot area in table 2. For instance, I need to find a formula that will choose to multiply the value in table 1 Row 5, Col 4 with table 2 row, 6, column 3. I want it to know which value to pull from table 1 to multiply with in table 2 so the zone and slope categories match up....Is this possible? What I have done as a workaround is to sort the data by the zone and then apply formulas that I developed for each zone. i.e. =IF(slope="<15%", area*$Q$8,IF(slope="15-30%",Area*$Q$9,IF(slope="30-45%",Area*$Q$11,IF(slope="45-60%",Area*$Q$12,IF(slope="60-100%",Area*$Q$13,IF(slope="100%",Area*$Q$14)))))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex 2 table formula
Excel 2007
Scariest (and only) formula used: =INDIRECT(Zone) INDIRECT(Slope) http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex 2 table formula
The difference between Mr. Herbert Seidenberg solution and the solution
provided by me is that, Mr. Herbert Seidenberg properly restructured the data but I have retrieved the desired result using the formula without changing the OP's Data Structure. -------------------- (Ms-Exl-Learner) -------------------- "Herbert Seidenberg" wrote: Excel 2007 Scariest (and only) formula used: =INDIRECT(Zone) INDIRECT(Slope) http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex 2 table formula
Agreed
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex 2 table formula
I tried both methods and neither have work successfully yet. However, the one
below seems the simplest. Can you walk me through how you created the names and lists and how the indirect feature works? I think my biggest trouble is figuring out what to do with both the "Slope" names...and the relationship between the two indirect's. Thanks so much for your help. "Herbert Seidenberg" wrote: Excel 2007 Scariest (and only) formula used: =INDIRECT(Zone) INDIRECT(Slope) http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Complex Pivot Table Function | Excel Discussion (Misc queries) | |||
Table with complex calculations | Excel Worksheet Functions | |||
Complex Pivot Table | Excel Discussion (Misc queries) | |||
Complex Vlookup Table | Excel Discussion (Misc queries) |