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