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.