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]

Welcome to the MrExcel board!

Mathematically, this isn't a real hard problem, but we do need to figure out exactly what you're asking. Consider this (admittedly crude) diagram:

Is this an accurate representation of your question? The length of the pipe is the bottom line for 28m. The depths are on the right. You're trying to find the 9.24 (x) value, and the equivalent value on the right. (If your 28m refers to the AB length, the logic is very similar.) Using the properties of similar triangles, we can calculate the x value. We're looking at the ABC triangle compared to the AXZ triangle. So the ratio of the right side BC to the right side XZ, is equal to the ratio of the base AC to the base AZ. i.e.

(3.4-2.2)/(3.4-3.0) = 28/x

Then solving for x we get: x = 9.33. This matches your answer, except for rounding. If this what you want, then I can come up with some Excel formulas that replicate this process for the various depths. This also ignores some issues like the diameter of the pipe, but let me know if that's important.

 

[J.Ty.]

What about this? You can test it here.

Book1
	A	B	C	D	E	F	G	H	I	J
1	start depth	5	bracket	0-1.5	1.51-3.00	3.51-4.50	4.51-6.00	6,01-7,50	7,51-9,00	9,01-10,50
2	end depth	5.5	unit price per meter	10.00 $	10.00 $	10.00 $	10.00 $	10.00 $	10.00 $	10.00 $
3	length	28
4	start in units	3.333333333	done in bracket	1	1	1	0.333333333	0	0	0
5	end in units	3.666666667	not done in bracket	1	1	1	0.666666667	0	0	0
6			price	-3.33 $	per	-0.333333333	meters
7			PRICE	280.00 $	PER	28	meters
Sheet1

Cell Formulas
Range	Formula
G4:J5, F4:F5, D4:D5, E4:E5	G4	=IF(H4=0,MAX($B4-COLUMNS($C$1:F$1)+1,0),1)
B4:B5	B4	=B1/1.5
D6	D6	=SUMPRODUCT(D2:J2*D4:J4)-SUMPRODUCT(D2:J2*D5:J5)
D7	D7	=IFERROR(D6*(B3/F6),B3*INDEX(D2:J2,CEILING.MATH(B4)))
F6	F6	=SUM(D4:J4)-SUM(D5:J5)
F7	F7	=B3

 

[WaltKerkmeer]

Hi Eric,

Thank you very much for your reply!

If I understand your representation correctly, the "pipeline length" value of 28 should be reflected under "A" to "B". The values I'm after are "A" to "X" and "X" to "B". Does this make sense? Please see below sketch of what the manholes and pipeline look like on site:

 

[WaltKerkmeer]

	Manhole Name/Pipe size (mm)	Calculated Line Length	MH Depth to Invert (m)	0-1.5m	1.51-3m	3.01-4.5m	4.51-6m
Upstream MH	SWMH 9.5		2.2​
1st Line	300​	28.0			18.76	9.24
2nd MH	SWMH 9.3		3.4​
2nd Line	375​	54.0
3rd MH	SWMH 9.2		3.1​

Below the "0-1.5m", "1.51-3m" -and so forth- columns, I would like to have the length of pipe that falls within that specific depth bracket.

 

[J.Ty.]

OK, data is there, but it fdoes not directly conform to your data layout. First test if you accept the calculations.

Book1
	A	B	C	D	E	F	G	H	I	J
1	start depth	1	bracket	0.00-1.50	1.51-3.00	3.01-4.50	4.51-6.00	6,01-7,50	7,51-9,00	9,01-10,50
2	end depth	3	unit price per meter	1.00 $	2.00 $	3.00 $	4.00 $	5.00 $	6.00 $	7.00 $
3	length	28
4	start in units	2	length in bracket	1	1	0	0	0	0	0
5	end in units	0.666666667	not done in bracket	0.666666667	0	0	0	0	0	0
6			length in bracket	7	21	0	0	0	0	0
7			price	7.00 $	42.00 $	0.00 $	0.00 $	0.00 $	0.00 $	0.00 $
8			Total price	49.00 $
Sheet1

Cell Formulas
Range	Formula
E4:J5, D4:D5	E4	=IF(F4=0,MAX($B4-COLUMNS($C$1:D$1)+1,0),1)
E6:J6, D6	E6	=$B$3*(E4-E5)/(SUM($D$4:$J$4)-SUM($D$5:$J$5))
E7:J7, D7	E7	=E6*E2
B4	B4	=MAX(B1,B2)/1.5
B5	B5	=MIN(B1,B2)/1.5
D8	D8	=SUM(D7:J7)

 

[WaltKerkmeer]

Hi J.Ty.,

Thanks for your reply, I really appreciate it.

Your sheet seems to be doing what I want to achieve. However in all honesty I can't say I fully understand your formulas and so I cannot test them.

Say, if I had a pipeline that started at manhole A at 1.2m depth, and finished at manhole B at 4.3m depth. Would your spreadsheet give the following values:

0-1.5m: 2.71
1.51-3m: 13.55
3.01-4.5m: 11.74

If it does, then this works! Looking forward to your reply.

 

[WaltKerkmeer]

Sorry forgot to add: the length of the pipeline in the above example would still be 28 meters.

 

[J.Ty.]

Up to rounding, it gives that. Have a look yourself: link.

J.Ty.

 

[Eric W]

I originally came up with a vertically oriented table, but I adapted it to match your layout from post 5. I changed the E1:H1 headings to just the number. But see if this works.

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		2.2
3	1st Line	300	28		0	18.66666667	9.333333333	0
4	2nd MH	SWMH 9.3		3.4
5	2nd Line	375	54		0	0	54	0
6	3rd MH	SWMH 9.2		3.1
Sheet2

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