Home 
Search 
Today's Posts 
#1




Complicated If Then / V Lookup / Match Statement...
I need a formula that would be able to calculate the distance from the start
of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#2




Complicated If Then / V Lookup / Match Statement...
Using your example data in Cells A1:F10, try this:
H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#3




Complicated If Then / V Lookup / Match Statement...
Yes, I can customize this formula to provide the total length for each
continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#4




Complicated If Then / V Lookup / Match Statement...
I'm glad I got you pointed in the right direction.....
Thanks for the feedback *********** Regards, Ron "ryesworld" wrote: Yes, I can customize this formula to provide the total length for each continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#5




Complicated If Then / V Lookup / Match Statement...
Hi, I ran into a problem with the formulas, the Start and Finish Cells are
not always returning the right numbers. Below is the table I get with your formulas, and the Start and Finish Cells for Report 200 are summing the wrong values (E4:F7). The formula I used for these is shown below the table. (Copied from E2 & E3) A B C D E F G 1 REPORT DIST CONT CODE START FINISH DISTANCE 2 100 1 S1 A 1 5  3 100 5 F1 A 1 5 4 4 200 1 S1 A 11 35  5 200 10 S2 A 11 35  6 200 15 F2 A 11 35 24 7 200 20 F1 A 11 35 24 Start: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000) Finish: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000) Any idea how to fix this? Thanks, Ryan "Ron Coderre" wrote: I'm glad I got you pointed in the right direction..... Thanks for the feedback *********** Regards, Ron "ryesworld" wrote: Yes, I can customize this formula to provide the total length for each continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#6




Complicated If Then / V Lookup / Match Statement...
See if this fixes it:
Start should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" S"&RIGHT($C2,1))*B$2:B$10000) Finish should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" F"&RIGHT($C2,1))*B$2:B$10000) Does that make it right? *********** Regards, Ron "ryesworld" wrote: Hi, I ran into a problem with the formulas, the Start and Finish Cells are not always returning the right numbers. Below is the table I get with your formulas, and the Start and Finish Cells for Report 200 are summing the wrong values (E4:F7). The formula I used for these is shown below the table. (Copied from E2 & E3) A B C D E F G 1 REPORT DIST CONT CODE START FINISH DISTANCE 2 100 1 S1 A 1 5  3 100 5 F1 A 1 5 4 4 200 1 S1 A 11 35  5 200 10 S2 A 11 35  6 200 15 F2 A 11 35 24 7 200 20 F1 A 11 35 24 Start: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000) Finish: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000) Any idea how to fix this? Thanks, Ryan "Ron Coderre" wrote: I'm glad I got you pointed in the right direction..... Thanks for the feedback *********** Regards, Ron "ryesworld" wrote: Yes, I can customize this formula to provide the total length for each continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#7




Complicated If Then / V Lookup / Match Statement...
Yes, that formula works. Thank you.
Can you see what is wrong with the formula below. It is for column G "Count", and looks at the Code for each row (column E). If that code matches any of the codes in row 1, the distance (column F) will be divided by the length (column B). For some reason, it only looks at the first code in Row 1? Thanks, Ryan Formula: {=IF(F3="","",(IF(E3=E$1:G$1,(F3/B3),F3)))} A B C D E F G 1 MATCH CODE X Y Z 2 REPORT LENGTH DIST CONT CODE DISTANCE COUNT 3 100 0.43 1 S1 W   4 100 0.43 5 F1 W 4 4.00 5 200 0.43 1 S1 Y   6 200 0.43 10 S2 X   7 200 0.43 15 F2 X 5 11.63 8 200 0.43 20 F1 Y 19 19.00 "Ron Coderre" wrote: See if this fixes it: Start should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" S"&RIGHT($C2,1))*B$2:B$10000) Finish should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" F"&RIGHT($C2,1))*B$2:B$10000) Does that make it right? *********** Regards, Ron "ryesworld" wrote: Hi, I ran into a problem with the formulas, the Start and Finish Cells are not always returning the right numbers. Below is the table I get with your formulas, and the Start and Finish Cells for Report 200 are summing the wrong values (E4:F7). The formula I used for these is shown below the table. (Copied from E2 & E3) A B C D E F G 1 REPORT DIST CONT CODE START FINISH DISTANCE 2 100 1 S1 A 1 5  3 100 5 F1 A 1 5 4 4 200 1 S1 A 11 35  5 200 10 S2 A 11 35  6 200 15 F2 A 11 35 24 7 200 20 F1 A 11 35 24 Start: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000) Finish: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000) Any idea how to fix this? Thanks, Ryan "Ron Coderre" wrote: I'm glad I got you pointed in the right direction..... Thanks for the feedback *********** Regards, Ron "ryesworld" wrote: Yes, I can customize this formula to provide the total length for each continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#8




Complicated If Then / V Lookup / Match Statement...
Try this:
G3: =IF(ISNUMBER(MATCH(E3,$E$1:$G$1,0)),F3/B3,F3) Does that help? *********** Regards, Ron "ryesworld" wrote: Yes, that formula works. Thank you. Can you see what is wrong with the formula below. It is for column G "Count", and looks at the Code for each row (column E). If that code matches any of the codes in row 1, the distance (column F) will be divided by the length (column B). For some reason, it only looks at the first code in Row 1? Thanks, Ryan Formula: {=IF(F3="","",(IF(E3=E$1:G$1,(F3/B3),F3)))} A B C D E F G 1 MATCH CODE X Y Z 2 REPORT LENGTH DIST CONT CODE DISTANCE COUNT 3 100 0.43 1 S1 W   4 100 0.43 5 F1 W 4 4.00 5 200 0.43 1 S1 Y   6 200 0.43 10 S2 X   7 200 0.43 15 F2 X 5 11.63 8 200 0.43 20 F1 Y 19 19.00 "Ron Coderre" wrote: See if this fixes it: Start should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" S"&RIGHT($C2,1))*B$2:B$10000) Finish should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" F"&RIGHT($C2,1))*B$2:B$10000) Does that make it right? *********** Regards, Ron "ryesworld" wrote: Hi, I ran into a problem with the formulas, the Start and Finish Cells are not always returning the right numbers. Below is the table I get with your formulas, and the Start and Finish Cells for Report 200 are summing the wrong values (E4:F7). The formula I used for these is shown below the table. (Copied from E2 & E3) A B C D E F G 1 REPORT DIST CONT CODE START FINISH DISTANCE 2 100 1 S1 A 1 5  3 100 5 F1 A 1 5 4 4 200 1 S1 A 11 35  5 200 10 S2 A 11 35  6 200 15 F2 A 11 35 24 7 200 20 F1 A 11 35 24 Start: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000) Finish: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000) Any idea how to fix this? Thanks, Ryan "Ron Coderre" wrote: I'm glad I got you pointed in the right direction..... Thanks for the feedback *********** Regards, Ron "ryesworld" wrote: Yes, I can customize this formula to provide the total length for each continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#9




Complicated If Then / V Lookup / Match Statement...
I am having a similar problem with an IF statement, I think...I am new to
Excel, so there may be a better solution to my formula than an IF... Here is what I need: profit sharing calculation, columns as follows: DOB, column B as mm/dd/yyyy Date of Hire, column C Dof Termination, column D hours worked, column E compensation, column G An employee must have worked 1 year, and 1000 hrs, not be terminated, be over 21 then share is 3% of compensation up to 90000, plus 8.7% of al compensation. No shares beyond 200000 in compensation. I have tried IF, IF AND, OR...nothing is working. I keep getting the error with the IFs that there are too many arguments. I posted a couple of them under "pensions"...but none of them worked. Help, please...thanks!  barbarat "Ron Coderre" wrote: See if this fixes it: Start should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" S"&RIGHT($C2,1))*B$2:B$10000) Finish should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" F"&RIGHT($C2,1))*B$2:B$10000) Does that make it right? *********** Regards, Ron "ryesworld" wrote: Hi, I ran into a problem with the formulas, the Start and Finish Cells are not always returning the right numbers. Below is the table I get with your formulas, and the Start and Finish Cells for Report 200 are summing the wrong values (E4:F7). The formula I used for these is shown below the table. (Copied from E2 & E3) A B C D E F G 1 REPORT DIST CONT CODE START FINISH DISTANCE 2 100 1 S1 A 1 5  3 100 5 F1 A 1 5 4 4 200 1 S1 A 11 35  5 200 10 S2 A 11 35  6 200 15 F2 A 11 35 24 7 200 20 F1 A 11 35 24 Start: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000) Finish: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000) Any idea how to fix this? Thanks, Ryan "Ron Coderre" wrote: I'm glad I got you pointed in the right direction..... Thanks for the feedback *********** Regards, Ron "ryesworld" wrote: Yes, I can customize this formula to provide the total length for each continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
#10




Complicated If Then / V Lookup / Match Statement...
Try this:
First, on a separate sheet (assuming sheet3) and beginning in Cell A1, put this table: (Full credit to JE McGimpsey for the rate table approach below) Comp Rate Rate Diff 0 3% 3.00% 90001 8.70% 5.70% 200000 0 8.70% Then, back on your table If Col_H holds the Shares calculation, then: H2: =IF(SUMPRODUCT((DATEDIF(B2,C2,"y")=21)*ISBLANK(D2)*(E2=1000)*(D ATEDIF(C2,TODAY(),"y")=1))=1,SUMPRODUCT((G2Sheet3!$A$2:$A$4),(G2Sheet3!$A$2:$A$4), Sheet3!$C$2:$C$4),0) Copy that formula down as needed. Does that help? *********** Regards, Ron "barbarat" wrote: I am having a similar problem with an IF statement, I think...I am new to Excel, so there may be a better solution to my formula than an IF... Here is what I need: profit sharing calculation, columns as follows: DOB, column B as mm/dd/yyyy Date of Hire, column C Dof Termination, column D hours worked, column E compensation, column G An employee must have worked 1 year, and 1000 hrs, not be terminated, be over 21 then share is 3% of compensation up to 90000, plus 8.7% of al compensation. No shares beyond 200000 in compensation. I have tried IF, IF AND, OR...nothing is working. I keep getting the error with the IFs that there are too many arguments. I posted a couple of them under "pensions"...but none of them worked. Help, please...thanks!  barbarat "Ron Coderre" wrote: See if this fixes it: Start should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" S"&RIGHT($C2,1))*B$2:B$10000) Finish should be: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*((C$2:C$10000)=" F"&RIGHT($C2,1))*B$2:B$10000) Does that make it right? *********** Regards, Ron "ryesworld" wrote: Hi, I ran into a problem with the formulas, the Start and Finish Cells are not always returning the right numbers. Below is the table I get with your formulas, and the Start and Finish Cells for Report 200 are summing the wrong values (E4:F7). The formula I used for these is shown below the table. (Copied from E2 & E3) A B C D E F G 1 REPORT DIST CONT CODE START FINISH DISTANCE 2 100 1 S1 A 1 5  3 100 5 F1 A 1 5 4 4 200 1 S1 A 11 35  5 200 10 S2 A 11 35  6 200 15 F2 A 11 35 24 7 200 20 F1 A 11 35 24 Start: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000) Finish: =SUMPRODUCT((A$2:A$10000=A2)*(D$2$10000=D2)*(LEFT(C$2:C$1000 0,1)="F")*B$2:B$10000) Any idea how to fix this? Thanks, Ryan "Ron Coderre" wrote: I'm glad I got you pointed in the right direction..... Thanks for the feedback *********** Regards, Ron "ryesworld" wrote: Yes, I can customize this formula to provide the total length for each continuous code on each row, and then tweak it so that it only calulates the distance on rows that have the Finish Codes (to prevent summing the lengths twice). You're the Excel Master. Brilliant! Thank You! "Ron Coderre" wrote: Using your example data in Cells A1:F10, try this: H1: Report H2: Code H3: Start H4: Finish H5: Distance I1: 100 I2: B I3: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0) I4: =SUMPRODUCT((A3:A10=I1)*(D310=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0) I5: =+I4I3 Does that give you something to work with? *********** Regards, Ron "ryesworld" wrote: I need a formula that would be able to calculate the distance from the start of a code to the end of a code. An example table is below. The start and end of a code is indicated in the CONT. column. (Start of first code = S1, end of first Code = F1, Start of second code = S2, end of second Code = F2, etc.) Maybe a table could be used (E1:F2) that shows the corresponding Start & Finish Codes. Two conditions need to be met before the length (in DIST column) is to be calculated, the Report numbers and the Codes must be the same for both rows. A B C D E F 1 S1 S2 2 REPORT DIST. CONT. CODE F1 F2 3 100 5 S1 B 4 100 10 S2 A 5 100 12 F1 B 6 100 14 C 7 100 16 F2 A 8 200 2 B 9 200 4 S1 B 10 200 10 F2 B In the table above:  Code B in report 100 would go for a distance of 7 (125).  Code A in report 100 would go for a distance of 6 (1610).  Code B in report 200 would go for a distance of 6 (104). Any ideas how to do this would be greatly appreciated! Thanks in advance. Ryan 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Match then lookup  Excel Worksheet Functions  
Complicated lookup function  Excel Worksheet Functions  
Lookup & match  Excel Discussion (Misc queries)  
Complex LookUp / Match Problem ??  Excel Worksheet Functions  
Lookup then Match and insert value from next column  Excel Worksheet Functions 