Results 1 to 3 of 3
Like Tree1Thanks
  • 1 Post By ebaines

Math Help - Calculating the length of trench where 1.5m depth is exceeded

  1. #1
    Newbie
    Joined
    Jul 2012
    From
    Leeds, UK
    Posts
    3

    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
    Attached Thumbnails Attached Thumbnails Calculating the length of trench where 1.5m depth is exceeded-001.jpg  
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor ebaines's Avatar
    Joined
    Jun 2008
    From
    Illinois
    Posts
    1,116
    Thanks
    327

    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.
    Thanks from peteryoull
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Jul 2012
    From
    Leeds, UK
    Posts
    3

    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
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Calculating Length
    Posted in the Calculus Forum
    Replies: 1
    Last Post: June 5th 2010, 10:59 PM
  2. Calculating Arc length of a curve.
    Posted in the Calculus Forum
    Replies: 4
    Last Post: February 6th 2010, 06:27 AM
  3. Replies: 1
    Last Post: November 10th 2008, 09:56 PM
  4. Replies: 3
    Last Post: November 10th 2008, 09:49 PM
  5. [SOLVED] [SOLVED] calculating the length of a shadow
    Posted in the Trigonometry Forum
    Replies: 5
    Last Post: May 10th 2006, 08:48 PM

Search Tags


/mathhelpforum @mathhelpforum