Find where break is in rods (oil well)

[nixadm]

I'm trying to make a spreadsheet that figures out automatically where the rod break is based on weight(oil well rod string)

For example a new full rod string weighs: 18,425
Current rod weight: 15,965

Obviously have a rod break and I am trying to make a sheet to calculate where the break is.

I can do it manually by just taking away the rods on the bottom until my weight reached the current weight of rods.

Image:
https://drive.google.com/open?id=0B6I8hCqtOOkJLWN3bnE4eUpQOGc


excel file:
https://drive.google.com/open?id=0B6I8hCqtOOkJZ1pQYmZIUDVRYkk

Thanks for any help.

 

[mikerickson]

15,965 / 18,425 = .8445

so the break is at the point 84.45% of the way down the rod.

 

[nixadm]

15,965 / 18,425 = .8445

so the break is at the point 84.45% of the way down the rod.

Thanks..

I'm trying to find which rod though...

 

[thisoldman]

What's the order of the rods? Will it always be largest diameter at the top to smallest diameter at the bottom?

 

[shg]

	A​	B​	C​	D​	E​	F​	G​	H​	I​
2​	Rod Dia​	# of rods​	Cumu Rods​	Length​	Wgt/ft​	Wgt/rod​	Wgt​	Cumu Wgt​
3​	1 1/2​	0​	-​	-​	6.52980​	163.25​	-​	-​
4​	1 1/4​	15​	15​	375​	4.52765​	113.19​	1,698​	1,698​
5​	1​	0​	15​	-​	2.89220​	72.31​	-​	1,698​
6​	1​	0​	15​	-​	2.89220​	72.31​	-​	1,698​
7​	7/8​	130​	145​	3,250​	2.21198​	55.30​	7,189​	8,887​
8​	3/4​	235​	380​	5,875​	1.62346​	40.59​	9,538​	18,425​
9​				9,500 ​			18,425 ​
10​						Curr Wgt​	15,965​
11​						Break at rod​	320.40​		G11: =PERCENTILE($C$3:$C$8, PERCENTRANK($H$3:$H$8, G10, 8)) + 1

Read that number as about halfway down the 320th rod in the string, counted from the top.

 

[nixadm]

What's the order of the rods? Will it always be largest diameter at the top to smallest diameter at the bottom?

The order is as listed...


1
0.875
0.75
1
1.25
1.5

 

[shg]

Reckon we don't know much about drill strings. In that case,

	A​	B​	C​	D​	E​	F​	G​
2​	Rod Dia​	# of Rods​	Wgt/ft​	Cumu Wgt​	Cumu Rods​	Depth​
3​				0​	0​	0​	<< need this row
4​	3/4​	235​	1.62346​	9,538​	235​	5,875​
5​	7/8​	130​	2.21198​	16,727​	365​	9,125​
6​	1​	0​	2.89220​	16,727​	365​	9,125​
7​	1 1/4​	15​	4.52765​	18,425​	380​	9,500​
8​	1 1/2​	0​	6.52980​	18,425​	380​	9,500​
9​
10​	Rod Dia​	Rod #​		Curr Wgt​	Break at rod​	Depth​
11​	3/4''​	1​		10​	1.25​	6.2​	A11: = INDEX(A3:A8, MATCH(E11, $E$3:$E$8) + 1)
12​							B11: =INT(E11 - INDEX($E$3:$E$8, MATCH(E11, $E$3:$E$8)))
13​							D11: Input
14​							E11: =PERCENTILE($E$3:$E$8, PERCENTRANK($D$3:$D$8, D11, 8)) + 1
15​							F11: =PERCENTILE($F$3:$F$8, PERCENTRANK($D$3:$D$8, D11, 8))

The workbook is at https://app.box.com/s/ugy2lxp3nymbvqm8q720mopvdfhvaamb

 

[shg]

That's wrong, sorry.

 

[thisoldman]

I added a cumulative weight column and went to a lookup solution. I also added a cell to hold the MATCH value for the INDEX+MATCH solution: I hate calculating values more than twice.

	A	B	C	D	E	F	G
1	Rods *N97
2	Diameter	# of rods	Length	Weight per foot	Weight per rod	Weight	Cumulative Weight
3	1"	0	-	2.8922	72.31	-	-
4	0.875	130	3,250	2.21198	55.30	7,189	7,189
5	0.75	235	5,875	1.62346	40.59	9,538	16,727
6	1	0	-	2.8922	72.31	-	16,727
7	1.25	15	375	4.52765	113.19	1,698	18,425
8	1.5		-	6.5298	163.25	-	18,425
9			9,500			18,425
10
11					Static weight of full rod string	18425
12					Current static weight	15965
13					Match row	3
14					Rod break at #	216 of 0.75 rods

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1
​

Worksheet Formulas Cell Formula G3 =F3 G4 =G3+F4 F13 =MATCH($F$12,G3:G8)+1 F14 =(INDEX(B3:B8,F13)+QUOTIENT(F12-INDEX(G3:G8,F13),INDEX(E3:E8,F13))-1)&" of "&INDEX(A3:A8,F13)&" rods" <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The workbook is at <a href="https://www.dropbox.com/s/zbf24gaxjc7nxu2/rods_01.xlsx?dl=0" target="_blank">https://www.dropbox.com/s/zbf24gaxjc7nxu2/rods_01.xlsx?dl=0

 

[Dave Patton]

I worked out a couple of solutions.
Unfortunately nixadm did not answer provide a concise example that we can use and the expected answer.
Are the lightest rods the ones that are lost?
Nixadm What would the manually calculated result for 15965 be with the following?

Excel 2010
	A	B	C
1			Total
2			Weight
3
4	15	113.19	1,697.85
5	130	55.3	7,189.00
6	235	40.59	9,538.65
7	380.00		18,425.50
8
1dd

Cell Formulas
Range	Formula
A7	=SUM(A4:A6)