View Single Post
  #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