OK. The following spreadsheet I have attached is a drainage design spreadsheet. On Tab 3-Pipe Design, there is column R - time of concentration.
Cell R32 is representative of how the formula for time of concentration must work, ie. the maximum time of concentration must be used. Cell R37 shows how I would like it to work (or atleast my attempts) so that excel finds the maximum TofC automatically.

The reason for this: redesigning a storm system can be very cumbersome. We had the spreadsheet, but adding a manhole in the middle of a pipe run resulted in a lot of re-work. I set out to find a way to make adding manholes / revisions more automated with excel by trying to break down components and re-combine them.

Each trunkline may have lateral sewer leads that feed it (think of a road side drainage system). The time of concentration is basically how much time it takes the water to get from manhole A to manhole B. Now if I have 2 lines that connect to manhole B (think of a Y-connection), where Manhole A connects to B and Manhole C connects to B. I want the excel function to find which run (a-b or c-b) has the max TofC and use that value.

Please see my spreadsheet from rapidshare (the attach function below would not work)

http://rapidshare.com/files/19700626...plate.zip.html


P.S. I did try indexing - see far far far right side of worksheet. But, I was unsuccessfull.


I am open to any suggestions.

thank You.

Chris