Formula to measure length of pipeline within specific depth brackets

[WaltKerkmeer]

Hi all,

I am struggling with a formula on Excel and was hoping you could help me with this. I work for a construction company and one of the things we do is the installation of manholes and pipelines.

We get paid a rate for pipelines between 0 and 1.5 meters deep. Then there is a separate rate for pipelines between 1.51 and 3 meters deep, then 3.01 to 4.5 meters deep and so forth.

I would like Excel to calculate how many meters of pipe are in the 0-1.5m, 1.51-3m and 3.01-4.5m brackets. At the moment I’m manually calculating this for each pipeline which is time consuming. An example below:

Known values:
Manhole A: 3.4m deep
Manhole B: 2.2m deep
Pipeline length: 28 meters

This falls in the 1.51-3m and 3.01-4.5m brackets.

3.4 – 3 = 0.4

3 – 2.2 = 0.8

0.4 + 0.8 = 1.2

0.4 / 1.2 = 0.33

0.33 * 28 = 9.24

So 9.24 meters of pipe is between 3.01-4.5m deep and the remaining length of pipe will be 1.51-3m deep. As you can imagine, it gets even worse when there’s a pipeline starting at say 1.2m deep and finishing at 4.3m deep, as in that situation the result would be in 3 different depth brackets (0-1.5m, 1.51-3m and 3.01-4.5m).

If there is anyone who could produce the formula to work this out, I would be forever grateful. This will save me a tonne of time.

Thanks in advance!

 

[Eric W]

Sorry, a minor tweak to handle depths in the opposite order:

Book1
	A	B	C	D	E	F	G	H
1		Manhole Name/Pipe size (mm)	Calculated Line Length	MH Depth to Invert (m)	1.5	3	4.5	6
2	Upstream MH	SWMH 9.5		1.2
3	1st Line	300	28		2.709677419	13.5483871	11.74193548	0
4	2nd MH	SWMH 9.3		4.3
5	2nd Line	375	54		5.225806452	26.12903226	22.64516129	0
6	3rd MH	SWMH 9.2		1.2
Sheet2

Cell Formulas
Range	Formula
E3:H3, E5:H5	E3	=MEDIAN(0,$C3-SUM($D3:D3),$C3*(E$1-MIN($D2:$D4))/ABS($D4-$D2)-SUM($D3:D3))

 

[WaltKerkmeer]

J.Ty. and Eric,

Thank you both a million. Your help has made my task so much easier!

All the best,

Walt Kerkmeer

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.

 

[Eric W]

Glad we could help!

 

[WaltKerkmeer]

Hi Eric,

Just thought I'd follow up on this and let you know that the formula you created has helped me enormously. I managed to create a formula on my own which I'm fairly proud of considering my limited knowledge in Excel, haha.

I would like to add another equation to the formula you created (if that's possible). In the below example, cell T94 calculates the length of pipe that falls within the 1.5-3m depth bracket (90.57m). This measure is from centre of manhole SWMH 9.1 to centre of manhole SWMH 9.

We are only going to get paid for actual length of pipe, i.e. from manhole wall to manhole wall. So this means we would have to subtract half of cell C93 and half of cell C95, and convert millimeters in column C to meters. If it makes it easier, I could change column C to show MH sizes in meters instead of millimeters, however ideally I'd like to keep it this way.

 

[WaltKerkmeer]

To clarify this a bit better, please see below sketch of the difference between measuring centre to centre vs. actual length of pipe. Your help will be much appreciated.

 

[Eric W]

Ugh. That sounds tricky! You essentially want to subtract out 1/2 of the diameter of the first manhole from the 1st non-zero value, and subtract out 1/2 the diameter of the second manhole from the the last non-zero number. And it's possible that both those numbers are the same. Since as you drag the formula to the right, it keeps track of the length of pipe used so far, if we subtract out something from the first length, all the rest will be thrown off.

It might be easier to subtract out the manhole lengths from the calculated line length (flat, I think?) before we start. Then we'd have to calculate the adjusted calculated line length (actual), and also the starting and ending depths. Do you get the actual length from the flat length and the gradient? When I do a direct multiplication the results I get are not quite what's shown on your sheet.

Let me know and I'll think about it a bit more.

 

[J.Ty.]

Is it the case, that you may calculate the price using the old formula and subtract the price of a level pipe at the depth of the manhole, and of legnth qual to its diameter?

J.Ty.

 

[WaltKerkmeer]

Hi Eric, that makes sense. Indeed we would have to subtract the manhole lengths from the flat line length first. This would in turn change the calculated gradient (because the calculated gradient is derived from subtracting the two pipe invert levels at each end by each other, and then dividing that by the calculated line length (flat)). This is how the calculated line length (flat) is calculated:

I'm getting the actual line length by this formula currently: =PRODUCT(H94,J94)+H94 which seems to me like a messy formula (I think it's messy because I refer to cell H94 twice) however I don't know how else I could do it.



@J.Ty. I could do that indeed, however I am hoping there is a way I can incorporate this calculation in the spreadsheet, before I do any price calculations. Reason is because for the pricing side of things, I have a completely different spreadsheet. I can't combine the two because they're both quite complex spreadsheets and it would get messy.

 

[WaltKerkmeer]

Here's a link to the spreadsheet if that makes it easier:

https://dempseywood-my.sharepoint.c...JOoyuMeGk-mDgBJ0R3kKyF36cDu1os-jviaw?e=rNzAZW