Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ryesworld
 
Posts: n/a
Default 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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ryesworld
 
Posts: n/a
Default 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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ryesworld
 
Posts: n/a
Default 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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

See if this fixes it:

Start should be:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ryesworld
 
Posts: n/a
Default 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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
barbarat
 
Posts: n/a
Default 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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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),(G2-Sheet3!$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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
barbarat
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

Ron:
I will give that a try, and let you know. Thanks so much!
Barb
--
barbarat


"Ron Coderre" wrote:

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),(G2-Sheet3!$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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ryesworld
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

No, this doesn't seem to work either. I don't understand. It's not
recognizing the codes that match. Any other ideas?

Thanks

"Ron Coderre" wrote:

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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

Well....there's probably some issue with the data. Try re-typing the values
in E1:G1 and re-typing one of the cells that should match. (Maybe there are
some inconspicuous spaces somewhere in the cells). Also, check the
references in the formula for errors.

Does that help?

***********
Regards,
Ron


"ryesworld" wrote:

No, this doesn't seem to work either. I don't understand. It's not
recognizing the codes that match. Any other ideas?

Thanks

"Ron Coderre" wrote:

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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ryesworld
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

Hi Ron,

I got it to work, I used a combination of your latest formula and the one I
made. I don't understand why it was so difficult. I re-entered (not copy &
paste) all the text in a new spreadsheet and got the same errors! It's
strange though, both my original formula and your formula worked if I only
referenced one cell (E1 instead of E1:G1). Following is the formula that I
came up with to get it to work... I don't understand why it works and the
others didn't.

=IF(F3="-","-",(IF(ISNUMBER(MATCH(E3,$E$1:$G$1,0)),F3/B3,F3)))

Thanks again for all your help. It was very helpful!
Ryan

"Ron Coderre" wrote:

Well....there's probably some issue with the data. Try re-typing the values
in E1:G1 and re-typing one of the cells that should match. (Maybe there are
some inconspicuous spaces somewhere in the cells). Also, check the
references in the formula for errors.

Does that help?

***********
Regards,
Ron


"ryesworld" wrote:

No, this doesn't seem to work either. I don't understand. It's not
recognizing the codes that match. Any other ideas?

Thanks

"Ron Coderre" wrote:

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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
barbarat
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

Ron:
I couldn't get that to work...I am not familiar enough; got a suggestion for
this:
=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7% *200000)),2))
It worked, but now here's the problem. I have a cap of $40000 for total
employee investments, including their contribution, match and profit share
(reduced to fit cap at profit sharing). The above formula gives me profit
share.
I have the match in column I...which will need to be added to the profit
match in column J, but capped at 40000. do I need a separate column, and if
so, would I use an IF, or can it be incorporated in the profit sharing, which
is where the overage is reduced...? Any suggestions? Thanks for all your
help.
Barbara
--
barbarat


"barbarat" wrote:

Ron:
I will give that a try, and let you know. Thanks so much!
Barb
--
barbarat


"Ron Coderre" wrote:

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),(G2-Sheet3!$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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan



  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

OK, Barbara....this is turning into a mini-project so let's do some
housekeeping so we can all understand how this model works when we're done.
We've already proven that we can make big, complicated formulas....now let's
make this model more intuitive to the casual user/reviewer.

Insert two rows at the top of the model, then...
N1: 12/31/2004

N2: 21
O2: 1
P2: 1000

M3: ProfShareStatus
N3: AgeTest
O3: TenureTest
P3: HrsTest
Q3: ActiveTest

M4: =--(SUM(N4:Q4)=4)
N4: =--(DATEDIF(B4,$N$1,"y")=$N$2)
O4: =--(DATEDIF(MIN(C4,$N$1),$N$1,"y")=$O$2)
P4: =--(E4=$P$2)
Q4: =--(ISBLANK(D4))
Copy those formulas down as far as needed

As you can see, the ProfShareStatus tests that there is a 1 in each of the 4
test fields. The formulas make it easier to isolate why someone would not
qualify for Profit Sharing.

Defninitely use JE McGimpsey's table approach. I put it in cells U1:W4
Comp Rate RateDiff
0 3% 3.00%
90001 8.70% 5.70%
200001 0 -8.70%

I3: Match
I4: =M4*SUMPRODUCT(--(G4$U$2:$U$4),(G4-$U$2:$U$4),$W$2:$W$4)
Copy that formula down as far as needed.

Now, I need some clarifications.
You mentioned that Col_J contains Profit Match.
Where does that value come from?

How is the $40,000 maximum used in the calculations?

That's all for now.

***********
Regards,
Ron


"barbarat" wrote:

Ron:
I couldn't get that to work...I am not familiar enough; got a suggestion for
this:
=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7% *200000)),2))
It worked, but now here's the problem. I have a cap of $40000 for total
employee investments, including their contribution, match and profit share
(reduced to fit cap at profit sharing). The above formula gives me profit
share.
I have the match in column I...which will need to be added to the profit
match in column J, but capped at 40000. do I need a separate column, and if
so, would I use an IF, or can it be incorporated in the profit sharing, which
is where the overage is reduced...? Any suggestions? Thanks for all your
help.
Barbara
--
barbarat


"barbarat" wrote:

Ron:
I will give that a try, and let you know. Thanks so much!
Barb
--
barbarat


"Ron Coderre" wrote:

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),(G2-Sheet3!$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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
barbarat
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

Hi Ron:
Thanks for the response...I will try this approach. I am quite new at this
so, it may take me a bit.
So, column J, the profit match, is the formula I gave. It is in addition to
the employer match (that formula is in column I, and is as follows:
=IF(DATEDIF(B2,"12/31/2004","y")<18,0,INT(MIN(H2,G2*0.03))+0.75*MAX(0,IN T(MIN(H2,G2*0.06)-G2*0.03))+0.5*MAX(0,INT(MIN(H2,G2*0.1)-G2*0.06)))
That says that employee must be 18 on 12/31/2004, and there is no match
beyond 10% of compensation. The match is $1 for $1 invested up to 3% of
compensation, $.75 for each dollar over 3% up to 6%, and $.5 for each dollar
between 7% and 10%.
Profit share is as stated, 3% up to 90000, plus 8.7% of total compensation.
No allocaton over 200000 compensation, and the employee must be 21 by
12/31/2004, have worked 1000 hours, been emplyed one year and not be
terminated.
Total match is capped at 40000, including the employee share, and the
reduction necessary comes from the profit sharing.
I have Date of termination as numerical date (01011984)...could that be a
problem?
Thanks for the ongoing support. I have made this entirely too complicated...
I will see if I can do the table, I am not very good at this...and will get
back. Thanks!
Barbara

--
barbarat


"Ron Coderre" wrote:

OK, Barbara....this is turning into a mini-project so let's do some
housekeeping so we can all understand how this model works when we're done.
We've already proven that we can make big, complicated formulas....now let's
make this model more intuitive to the casual user/reviewer.

Insert two rows at the top of the model, then...
N1: 12/31/2004

N2: 21
O2: 1
P2: 1000

M3: ProfShareStatus
N3: AgeTest
O3: TenureTest
P3: HrsTest
Q3: ActiveTest

M4: =--(SUM(N4:Q4)=4)
N4: =--(DATEDIF(B4,$N$1,"y")=$N$2)
O4: =--(DATEDIF(MIN(C4,$N$1),$N$1,"y")=$O$2)
P4: =--(E4=$P$2)
Q4: =--(ISBLANK(D4))
Copy those formulas down as far as needed

As you can see, the ProfShareStatus tests that there is a 1 in each of the 4
test fields. The formulas make it easier to isolate why someone would not
qualify for Profit Sharing.

Defninitely use JE McGimpsey's table approach. I put it in cells U1:W4
Comp Rate RateDiff
0 3% 3.00%
90001 8.70% 5.70%
200001 0 -8.70%

I3: Match
I4: =M4*SUMPRODUCT(--(G4$U$2:$U$4),(G4-$U$2:$U$4),$W$2:$W$4)
Copy that formula down as far as needed.

Now, I need some clarifications.
You mentioned that Col_J contains Profit Match.
Where does that value come from?

How is the $40,000 maximum used in the calculations?

That's all for now.

***********
Regards,
Ron


"barbarat" wrote:

Ron:
I couldn't get that to work...I am not familiar enough; got a suggestion for
this:
=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7% *200000)),2))
It worked, but now here's the problem. I have a cap of $40000 for total
employee investments, including their contribution, match and profit share
(reduced to fit cap at profit sharing). The above formula gives me profit
share.
I have the match in column I...which will need to be added to the profit
match in column J, but capped at 40000. do I need a separate column, and if
so, would I use an IF, or can it be incorporated in the profit sharing, which
is where the overage is reduced...? Any suggestions? Thanks for all your
help.
Barbara
--
barbarat


"barbarat" wrote:

Ron:
I will give that a try, and let you know. Thanks so much!
Barb
--
barbarat


"Ron Coderre" wrote:

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),(G2-Sheet3!$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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Complicated If Then / V Lookup / Match Statement...

Hi, Barbara

If you followed my other recommendations, then try this:
I4: =MIN(40000-J4,M4*SUMPRODUCT(--(G4$U$2:$U$4),(G4-$U$2:$U$4),
$W$2:$W$4))

Copy that down as far as needed.

That formula determines how much Profit Sharing can be paid without
exceeding the $40k max for combined Profit Sharing and Profit Match.

Did I get that right?

***********
Regards,
Ron


"barbarat" wrote:

Hi Ron:
Thanks for the response...I will try this approach. I am quite new at this
so, it may take me a bit.
So, column J, the profit match, is the formula I gave. It is in addition to
the employer match (that formula is in column I, and is as follows:
=IF(DATEDIF(B2,"12/31/2004","y")<18,0,INT(MIN(H2,G2*0.03))+0.75*MAX(0,IN T(MIN(H2,G2*0.06)-G2*0.03))+0.5*MAX(0,INT(MIN(H2,G2*0.1)-G2*0.06)))
That says that employee must be 18 on 12/31/2004, and there is no match
beyond 10% of compensation. The match is $1 for $1 invested up to 3% of
compensation, $.75 for each dollar over 3% up to 6%, and $.5 for each dollar
between 7% and 10%.
Profit share is as stated, 3% up to 90000, plus 8.7% of total compensation.
No allocaton over 200000 compensation, and the employee must be 21 by
12/31/2004, have worked 1000 hours, been emplyed one year and not be
terminated.
Total match is capped at 40000, including the employee share, and the
reduction necessary comes from the profit sharing.
I have Date of termination as numerical date (01011984)...could that be a
problem?
Thanks for the ongoing support. I have made this entirely too complicated...
I will see if I can do the table, I am not very good at this...and will get
back. Thanks!
Barbara

--
barbarat


"Ron Coderre" wrote:

OK, Barbara....this is turning into a mini-project so let's do some
housekeeping so we can all understand how this model works when we're done.
We've already proven that we can make big, complicated formulas....now let's
make this model more intuitive to the casual user/reviewer.

Insert two rows at the top of the model, then...
N1: 12/31/2004

N2: 21
O2: 1
P2: 1000

M3: ProfShareStatus
N3: AgeTest
O3: TenureTest
P3: HrsTest
Q3: ActiveTest

M4: =--(SUM(N4:Q4)=4)
N4: =--(DATEDIF(B4,$N$1,"y")=$N$2)
O4: =--(DATEDIF(MIN(C4,$N$1),$N$1,"y")=$O$2)
P4: =--(E4=$P$2)
Q4: =--(ISBLANK(D4))
Copy those formulas down as far as needed

As you can see, the ProfShareStatus tests that there is a 1 in each of the 4
test fields. The formulas make it easier to isolate why someone would not
qualify for Profit Sharing.

Defninitely use JE McGimpsey's table approach. I put it in cells U1:W4
Comp Rate RateDiff
0 3% 3.00%
90001 8.70% 5.70%
200001 0 -8.70%

I3: Match
I4: =M4*SUMPRODUCT(--(G4$U$2:$U$4),(G4-$U$2:$U$4),$W$2:$W$4)
Copy that formula down as far as needed.

Now, I need some clarifications.
You mentioned that Col_J contains Profit Match.
Where does that value come from?

How is the $40,000 maximum used in the calculations?

That's all for now.

***********
Regards,
Ron


"barbarat" wrote:

Ron:
I couldn't get that to work...I am not familiar enough; got a suggestion for
this:
=IF(OR(DATEDIF(B2,"12/31/04","y")<21,E2<1000,D20),0,ROUND(IF(G2<=90000,11. 7%*G2,IF(G2<=200000,3%*90000+8.7%*G2,3%*90000+8.7% *200000)),2))
It worked, but now here's the problem. I have a cap of $40000 for total
employee investments, including their contribution, match and profit share
(reduced to fit cap at profit sharing). The above formula gives me profit
share.
I have the match in column I...which will need to be added to the profit
match in column J, but capped at 40000. do I need a separate column, and if
so, would I use an IF, or can it be incorporated in the profit sharing, which
is where the overage is reduced...? Any suggestions? Thanks for all your
help.
Barbara
--
barbarat


"barbarat" wrote:

Ron:
I will give that a try, and let you know. Thanks so much!
Barb
--
barbarat


"Ron Coderre" wrote:

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),(G2-Sheet3!$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:D$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:D$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:D$10000=D2)*(LEFT(C$2:C$1000 0,1)="S")*B$2:B$10000)

Finish:
=SUMPRODUCT(--(A$2:A$10000=A2)*(D$2:D$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)*(D3:D10=I2)*(LEFT(C3:C10,1)="S")*B3:B1 0)
I4: =SUMPRODUCT(--(A3:A10=I1)*(D3:D10=I2)*(LEFT(C3:C10,1)="F")*B3:B1 0)
I5: =+I4-I3

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 (12-5).
- Code A in report 100 would go for a distance of 6 (16-10).
- Code B in report 200 would go for a distance of 6 (10-4).

Any ideas how to do this would be greatly appreciated!
Thanks in advance.
Ryan

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Complicated lookup function chrisrowe_cr Excel Worksheet Functions 4 July 19th 05 05:52 PM
Lookup & match daniel chen Excel Discussion (Misc queries) 2 June 19th 05 01:18 PM
Complex LookUp / Match Problem ?? carl Excel Worksheet Functions 2 May 2nd 05 08:53 PM
Lookup then Match and insert value from next column Tenacity Excel Worksheet Functions 3 March 4th 05 03:49 AM


All times are GMT +1. The time now is 11:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"