1 Attachment(s)

Calculating the length of trench where 1.5m depth is exceeded

Hi all,

I am trying to write a formula in excel 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.

(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.)

Thanks! Peter

Re: Calculating the length of trench where 1.5m depth is exceeded

First you need a way of calculating the depth at any point x along the trench. Given the gradient of the pipe (call it G_p), the gradient of the surface (G_s), and the starting depth of h1, the depth at point x along the trench is d(x) = h1 + (G_p-G_s)x. Please note here that positive values of gradient are for going downhill.

You have four cases, all of which can be combined into a single Excel expression, but let's analyze them individually first:

1. If the starting depth h1 > 1.5m, then:

a. if the ending depth d(L) is greater than 1.5 m, then the length = L

b. if the ending depth d(L) is less than 1.5m, then the point where the depth is 1.5m is at x = (1.5m-h1)/(G_p-G_s), and the length is x

2. If the starting depth h1 < 1.5m, then:

a. if the ending depth d(L) is less than 1.5m then length = 0

b. if the ending depth d(L) is greater than 1.5m then the point where the depth = 1.5m is at x = (1.5m-h1)/(G_p-G_s), and the length is L-x

You can make a single equation out of all this as follows:

length = If(h1>1.5, if (h1+L(G_p-G_s)>1.5, L,(1.5-h1)/(G_p-G_s)),if (h1+L(G_p-G_s)<=1.5,0,L-(1.5-h1)/(G_p-G_s)))

Hope this helps.

Re: Calculating the length of trench where 1.5m depth is exceeded

Thats amazing! Thanks very much for all the help. the explaination was perfect and I can see I was nearly there with what I was doing, just missed some crucial things.

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))))

Respect, Thanks and Blessings for the help!

Peter