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!

 

[WaltKerkmeer]

That didn't work for some reason, this is the link:

 

[Eric W]

I'll play around a bit and see what I can come up with. In the meantime, =PRODUCT(H94,J94)+H94 can be written as:

=(J94+1)*H94

 

[WaltKerkmeer]

That's great, and nice and short!

 

[Eric W]

I tried downloading the spreadsheet, but it didn't help much. It was set to View only, so I could see the data, but not the formulas. Can you try using the XL2BB tool to show a sample of your sheet? I suspect it would be easiest to add some helper columns, but I can't tell for sure until I can see how the values are calculated.

 

[WaltKerkmeer]

Hi Eric,

Please see below, hope this works:

Book1
	A	B	C	D	E	F	G	H	I	J	K		M	N	O	P			S	T	U	V
1		Manhole Name/Pipe size (mm)	MH Size (mm)/Pipe Class	Coord. N	Coord. E	X dist.	Y dist.	Calculated Line Length (flat)	Calculated Line Length (actual)	Calculated Gradient (%)	Cement Treated Bedding/ Anchor Block		Lid Level RL	Pipe Inlet RL	Pipe Outlet RL	MH Depth to Invert (m)			0-1.5m depth pipe	1.5-3m depth pipe	3-4.5m depth pipe	4.5-6m depth pipe
2																			1.5	3.0	4.5	6.0
3	Upstream MH	SWMH 48.1	1050	5950715.2	1749635.13								73.68		72.37	1.31
4	1st Line	225	Class 4			17.84	-10.59	20.7	23.28	12.2%	CTB								4.70	18.57	0.00	0.00
5	2nd MH	SWMH 48.2	1050	5950697.36	1749624.54								72.03	69.84	69.78	2.25
6	2nd Line	300	Class 4			7.99	-7.57	11.0	11.34	3.0%	None								0.00	11.34	0.00	0.00
7	3rd MH	SWMH 48.3	1050	5950689.37	1749616.97								71	69.45	69.3	1.7
8	3rd Line		Class 4								None
9	4th MH	SWMH L2/7
10
11	Upstream MH	SWMH 47A	1050	5950737.27	1749647.7								74.36		73.06	1.3
12	1st Line	375	Class 2			-45.14	22.81	50.6	52.30	3.4%	None								45.47	6.82	0.00	0.00
13	2nd MH	SWMH 47	1050	5950782.41	1749670.51								72.81	71.34	71.28	1.53
14	2nd Line	375	Class 2			-15.75	7.48	17.4	18.02	3.3%	None								7.21	10.81	0.00	0.00
15	3rd MH	SWMH 15.3	1050	5950798.16	1749677.99								72.08	70.7	70.6	1.48
16	3rd Line	375	Class 2			-49.23	11.67	50.6	53.22	5.2%	None								7.60	45.62	0.00	0.00
17	4th MH	SWMH 15.2	1050	5950847.39	1749689.66								69.54	67.97	67.92	1.62
18	4th Line	375	Class 2			-24.04	10.07	26.1	27.73	6.4%	None								0.00	27.73	0.00	0.00
19	5th MH	SWMH 15	1050	5950871.43	1749699.73								67.84	66.25	66.21	1.63
20	5th Line	375	Class 2			-31.38	22.98	38.9	41.86	7.6%	None								17.13	24.74	0.00	0.00
21	6th MH	SWMH 14.1	1050	5950902.81	1749722.71								64.6	63.24	63.19	1.41
22	6th Line	450	Class 2			-17.32	9	19.5	22.79	16.8%	CTB								1.64	21.15	0.00	0.00
23	7th MH	SWMH 14	1050	5950920.13	1749731.71								62.53	59.92	59.87	2.66
24	7th Line	525	Class 2			13.47	37.59	39.9	40.53	1.5%	None								0.00	40.53	0.00	0.00
25	8th MH	SWMH 16	1050	5950906.66	1749769.3								60.93	59.27	59.13	1.8
26	8th Line	525	Class 2			10.1	31.33	32.9	34.75	5.6%	None								0.00	34.75	0.00	0.00
27	9th MH	SWMH 18	1800	5950896.56	1749800.63								59.41	57.3	57.19	2.22
28	9th Line	525	Class 2			5.04	8.32	9.7	9.96	2.4%	None								0.00	9.96	0.00	0.00
29	10th MH	SWMH 18A	1500	5950891.52	1749808.95								59.11	56.96	56.9	2.21
30	10th Line	525	Class 2			13.1	41.21	43.2	43.74	1.2%	None								0.00	43.74	0.00	0.00
31	11th MH	SWMH 19	1050	5950878.42	1749850.16								58.45	56.4	56.23	2.22
32	11th Line	525	Class 2			16.93	33.78	37.8	40.17	6.3%	None								0.00	40.17	0.00	0.00
33	12th MH	SWMH 19.1	1050	5950861.49	1749883.94								55.61	53.85	53.29	2.32
34	12th Line	525	Class 2			1.100000001	8.05	8.1	8.21	1.1%	None								0.00	8.21	0.00	0.00
35	13th MH	SWMH 19.2	1050	5950860.39	1749891.99								55.01	53.2	53.14	1.87
36	13th Line	525	Class 2			9.89	26.49	28.3	30.36	7.4%	None								0.00	30.36	0.00	0.00
37	14th MH	SWMH 20	1800	5950850.5	1749918.48								53.32	51.06	51.01	2.31
38	14th Line	630	SDR17			43.97	-12.23	45.6	63.78	39.7%	Anc. Block								0.00	19.05	41.42	3.31
39	Outlet Structure	Outlet 3		5950806.53	1749906.25									32.87
40
SW

Cell Formulas
Range	Formula
P3, P37, P35, P33, P31, P29, P27, P25, P23, P21, P19, P17, P15, P13, P11, P7, P5	P3	=M3-O3
F4, F38, F36, F34, F32, F30, F28, F26, F24, F22, F20, F18, F16, F14, F12, F6	F4	=D3-D5
G4, G38, G36, G34, G32, G30, G28, G26, G24, G22, G20, G18, G16, G14, G12, G6	G4	=E5-E3
H4, H38, H36, H34, H32, H30, H28, H26, H24, H22, H20, H18, H16, H14, H12, H6	H4	=SQRT(F4^2+G4^2)
I4, I38, I36, I34, I32, I30, I28, I26, I24, I22, I20, I18, I16, I14, I12, I6	I4	=(J4+1)*H4
J4, J38, J36, J34, J32, J30, J28, J26, J24, J22, J20, J18, J16, J14, J12, J6	J4	=(O3-N5)/H4
K4, K38, K36, K34, K32, K30, K28, K26, K24, K22, K20, K18, K16, K14, K12, K8, K6	K4	=IF(J4<0.1,"None",IF(J4<=0.2,"CTB",IF(J4>0.2,"Anc. Block")))
S4, S38, S36, S34, S32, S30, S28, S26, S24, S22, S20, S18, S16, S14, S12, S6	S4	=MEDIAN(0,$I4-SUM($P4:P4),$I4*(S$2-MIN($P3:$P5))/ABS($P5-$P3)-SUM($P4:P4))
T4:V4, T38:V38, T36:V36, T34:V34, T32:V32, T30:V30, T28:V28, T26:V26, T24:V24, T22:V22, T20:V20, T18:V18, T16:V16, T14:V14, T12:V12, T6:V6	T4	=MEDIAN(0,$I4-SUM($P4:S4),$I4*(T$2-MIN($P3:$P5))/ABS($P5-$P3)-SUM($P4:S4))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=SW!$A$1:$AC$293	P3, F4:K4, S4:V4, P5, F6:K6, S6:V6, P7, K8, P11, F12:K12, S12:V12, P13, F14:K14, S14:V14, P15, F16:K16, S16:V16, P17, F18:K18, S18:V18, P19, F20:K20, S20:V20, P21, F22:K22, S22:V22, P23, F24:K24, S24:V24, P25, F26:K26, S26:V26, P27, F28:K28, S28:V28, P29, F30:K30, S30:V30, P31, F32:K32, S32:V32, P33, F34:K34, S34:V34, P35, F36:K36, S36:V36, P37, F38:K38, S38:V38

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AA4:XFD4,AA6:XFD277,S7:Z9,S11:Z11,S13:Z13,S15:Z15,S17:Z17,S19:Z19,S21:Z21,S23:Z23,S25:Z25,S27:Z27,S29:Z29,S31:Z31,S33:Z33,S35:Z35,S37:Z37,S39:Z39,S41:Z41,S43:Z43,S45:Z45,S47:Z47,S49:Z49,S51:Z51,S53:Z53,S55:Z55,S57:Z57,S59:Z59,S61:Z61,S63:Z63,S65:Z65,S67:Z67	Expression	=MOD(ROW(),2)=1	text	NO

 

[Eric W]

Sorry I've taken a while to get back to this, it sort of feels like trigonometry homework! I'm definitely a math geek, but this trends towards the drudgery side of math.

Anyway, this looks like it depends on how you enter the depth values, O2 and M5. Is this depth measured from the bottom of the pipe, or the top of the pipe? Also, is this depth measured from the center of the manhole, or the edge of the manhole? The first question is almost immaterial, but the second one is key. If the depth is measured from the edge (which seems likely since the gradient is calculated from the Pipe Outlet RL to Pipe Inlet RL), then the depths we use in the S4 formulas are already correct, and we just have to adjust the Calculated Line Length (Flag). To do that, just change the H4 formula to:

=SQRT(F4^2+G4^2)-(B4+B6)/2000

If the depths in the P column are not from the edge of the manhole, then I don't quite understand the relationship between the M, N, O, P values. If you could show a diagram, that would help.

 

[WaltKerkmeer]

Hi Eric,

No problem, I appreciate your help.

I had the spreadsheet reviewed internally and changes were made to correct some formula's I had overlooked. One of the things that had to be changed was the formula in cell H4, like you pointed out - we were measuring from center of manhole to center of manhole.

I think I have everything sorted now. Thanks very much for your help with this.

 

[Eric W]

You're welcome, I'm glad you got it sorted. Thanks for the feedback.