Calculating the length of trench where 1.5m is exceeded in excel

[Peteryoull]

Hi all,

I am trying to write a formula to calculate the length of a trench (as in to lay a pipe in the ground). Where the trench is over 1.5m deep, there needs to be wooden support to prevent the trench collapsing. I have hundereds of these trenches to analyse, so it will take too long to calculate by hand.

For each trench I have the depth at the start of the trench, the depth of the end of the trench and the length of the trench. I need an equation to calculate the length of trench where 1.5m depth is exceeded, in excel. Ive been sat looking at this for a few hours now and just seem to be going round in circles, just in terms of the maths side of it more than the excel bit. Ive attached this diagram to help understand what i mean and give 3 example cases. Ideally the equation would solve all 3 cases in one, removing the need to change the chosen equation when copying it down across the many trenches im looking at.
001.jpg
(in reference the the pictures, i have h1, h2 and l. the gradient of the bottom of the trench will always be 1/200 but the gradient of the top will vary.)
I appreciate this is really more a maths question, but ive always had such good results asking on here I thought ide give it a try!

Thanks! Peter

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[zbor]

Can you upload small example workbook with your inputs and desired output?

[Peteryoull]

Yes certainly,

This book contains the first 78 terms that ive looked at, and done by hand. There are another atleast 1000 to assess and there must be a simple equation to calculate it!

Thanks!

Sample workbook.xlsx

[shg]

This seems unncessarily complicated:

Please Login or Register  to view this content.

In D4 and copied down,

=IF(AND(A4<=d, B4<=d), 1, IF(AND(A4>=d, B4>=d), 0, IF(A4<B4, (d-A4)/(B4-A4), 1 - (d-A4)/(B4-A4)))) * C4

[Peteryoull]

Thats amazing! thanks very much! I new it would be pretty simple,

This is the code ive ended up with if your interested:
where: I2=h1, J2=h2, K2= gradient of pipe, E2= gradient of ground surface, B2=length of trench.

=IF(I2>1.5,IF(J2>1.5,B2,((1.5-I2)/(K2-E2))),IF(J2<=1.5,0,B2-((1.5-I2)/(K2-E2))))

Basically the same, but in a different order. Respect and blessings for the help.

[shg]

Similarly,

=C4*CHOOSE(2*(A4<=d)+(B4<=d)+1, 0, 1-(d-A4)/(B4-A4), (d-A4)/(B4-A4), 1)