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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Rod Dia​

[/td][td="bgcolor:#F3F3F3"]

# of rods​

[/td][td="bgcolor:#F3F3F3"]

Cumu Rods​

[/td][td="bgcolor:#F3F3F3"]

Length​

[/td][td="bgcolor:#F3F3F3"]

Wgt/ft​

[/td][td="bgcolor:#F3F3F3"]

Wgt/rod​

[/td][td="bgcolor:#F3F3F3"]

Wgt​

[/td][td="bgcolor:#F3F3F3"]

Cumu Wgt​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1 1/2​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td]

6.52980​

[/td][td="bgcolor:#E5E5E5"]

163.25​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1 1/4​

[/td][td]

15​

[/td][td="bgcolor:#E5E5E5"]

15​

[/td][td="bgcolor:#E5E5E5"]

375​

[/td][td]

4.52765​

[/td][td="bgcolor:#E5E5E5"]

113.19​

[/td][td="bgcolor:#E5E5E5"]

1,698​

[/td][td="bgcolor:#E5E5E5"]

1,698​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]

15​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td]

2.89220​

[/td][td="bgcolor:#E5E5E5"]

72.31​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td="bgcolor:#E5E5E5"]

1,698​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]

15​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td]

2.89220​

[/td][td="bgcolor:#E5E5E5"]

72.31​

[/td][td="bgcolor:#E5E5E5"]

-​

[/td][td="bgcolor:#E5E5E5"]

1,698​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

7/8​

[/td][td]

130​

[/td][td="bgcolor:#E5E5E5"]

145​

[/td][td="bgcolor:#E5E5E5"]

3,250​

[/td][td]

2.21198​

[/td][td="bgcolor:#E5E5E5"]

55.30​

[/td][td="bgcolor:#E5E5E5"]

7,189​

[/td][td="bgcolor:#E5E5E5"]

8,887​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

3/4​

[/td][td]

235​

[/td][td="bgcolor:#E5E5E5"]

380​

[/td][td="bgcolor:#E5E5E5"]

5,875​

[/td][td]

1.62346​

[/td][td="bgcolor:#E5E5E5"]

40.59​

[/td][td="bgcolor:#E5E5E5"]

9,538​

[/td][td="bgcolor:#E5E5E5"]

18,425​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]

9,500 ​

[/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]

18,425 ​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Curr Wgt​

[/td][td]

15,965​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Break at rod​

[/td][td="bgcolor:#E5E5E5"]

320.40​

[/td][td][/td][td]G11: =PERCENTILE($C$3:$C$8, PERCENTRANK($H$3:$H$8, G10, 8)) + 1[/td][/tr]
[/table]

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,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Rod Dia​

[/td][td="bgcolor:#F3F3F3"]

# of Rods​

[/td][td="bgcolor:#F3F3F3"]

Wgt/ft​

[/td][td="bgcolor:#F3F3F3"]

Cumu Wgt​

[/td][td="bgcolor:#F3F3F3"]

Cumu Rods​

[/td][td="bgcolor:#F3F3F3"]

Depth​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td]<< need this row[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3/4​

[/td][td]

235​

[/td][td]

1.62346​

[/td][td="bgcolor:#E5E5E5"]

9,538​

[/td][td="bgcolor:#E5E5E5"]

235​

[/td][td="bgcolor:#E5E5E5"]

5,875​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

7/8​

[/td][td]

130​

[/td][td]

2.21198​

[/td][td="bgcolor:#E5E5E5"]

16,727​

[/td][td="bgcolor:#E5E5E5"]

365​

[/td][td="bgcolor:#E5E5E5"]

9,125​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1​

[/td][td]

0​

[/td][td]

2.89220​

[/td][td="bgcolor:#E5E5E5"]

16,727​

[/td][td="bgcolor:#E5E5E5"]

365​

[/td][td="bgcolor:#E5E5E5"]

9,125​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1 1/4​

[/td][td]

15​

[/td][td]

4.52765​

[/td][td="bgcolor:#E5E5E5"]

18,425​

[/td][td="bgcolor:#E5E5E5"]

380​

[/td][td="bgcolor:#E5E5E5"]

9,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1 1/2​

[/td][td]

0​

[/td][td]

6.52980​

[/td][td="bgcolor:#E5E5E5"]

18,425​

[/td][td="bgcolor:#E5E5E5"]

380​

[/td][td="bgcolor:#E5E5E5"]

9,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td="bgcolor:#F3F3F3"]

Rod Dia​

[/td][td="bgcolor:#F3F3F3"]

Rod #​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Curr Wgt​

[/td][td="bgcolor:#F3F3F3"]

Break at rod​

[/td][td="bgcolor:#F3F3F3"]

Depth​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#E5E5E5"]

3/4''​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]

1.25​

[/td][td="bgcolor:#E5E5E5"]

6.2​

[/td][td]A11: = INDEX(A3:A8, MATCH(E11, $E$3:$E$8) + 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B11: =INT(E11 - INDEX($E$3:$E$8, MATCH(E11, $E$3:$E$8)))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]D11: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]E11: =PERCENTILE($E$3:$E$8, PERCENTRANK($D$3:$D$8, D11, 8)) + 1[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]F11: =PERCENTILE($F$3:$F$8, PERCENTRANK($D$3:$D$8, D11, 8))[/td][/tr]
[/table]

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
Rods *N97

<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>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Diameter[/TD]
[TD="align: center"]# of rods[/TD]
[TD="align: center"]Length[/TD]
[TD="align: center"]Weight per foot[/TD]
[TD="align: center"]Weight per rod[/TD]
[TD="align: center"]Weight[/TD]
[TD="align: center"]Cumulative Weight[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1"[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]0[/TD]
[TD="bgcolor: <a href=, align: center"] -
[/TD]
[TD="align: center"]2.8922[/TD]
[TD="align: center"]72.31[/TD]
[TD="bgcolor: <a href=, align: center"] -
[/TD]
[TD="bgcolor: <a href=, align: center"] -
[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]0.875[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]130[/TD]
[TD="bgcolor: <a href=, align: center"] 3,250
[/TD]
[TD="align: center"]2.21198[/TD]
[TD="align: center"]55.30[/TD]
[TD="bgcolor: <a href=, align: center"] 7,189
[/TD]
[TD="bgcolor: <a href=, align: center"] 7,189
[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]0.75[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]235
[/TD]
[TD="bgcolor: <a href=, align: center"] 5,875
[/TD]
[TD="align: center"]1.62346[/TD]
[TD="align: center"]40.59[/TD]
[TD="bgcolor: <a href=, align: center"] 9,538
[/TD]
[TD="bgcolor: <a href=, align: center"]16,727
[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]0[/TD]
[TD="bgcolor: <a href=, align: center"] -
[/TD]
[TD="align: center"]2.8922[/TD]
[TD="align: center"]72.31[/TD]
[TD="bgcolor: <a href=, align: center"] -
[/TD]
[TD="bgcolor: <a href=, align: center"]16,727
[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1.25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"]15[/TD]
[TD="bgcolor: <a href=, align: center"] 375
[/TD]
[TD="align: center"]4.52765[/TD]
[TD="align: center"]113.19[/TD]
[TD="bgcolor: <a href=, align: center"] 1,698
[/TD]
[TD="bgcolor: <a href=, align: center"]18,425
[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: center"][/TD]
[TD="bgcolor: <a href=, align: center"] -
[/TD]
[TD="align: center"]6.5298[/TD]
[TD="align: center"]163.25[/TD]
[TD="bgcolor: <a href=, align: center"] -
[/TD]
[TD="bgcolor: <a href=, align: center"]18,425
[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 9,500 [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 18,425 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Static weight of full rod string[/TD]
[TD="align: right"]18425[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Current static weight[/TD]
[TD="align: right"]15965[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Match row[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Rod break at #[/TD]
[TD="bgcolor: #FBDDDD"]216 of 0.75 rods[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]G3
[/TH]
[TD="align: left"]=F3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: <a href="]G4
[/TH]
[TD="align: left"]=G3+F4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: <a href="]F13
[/TH]
[TD="align: left"]=MATCH($F$12,G3:G8)+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: <a href="]F14
[/TH]
[TD="align: left"]=(INDEX(B3:B8,F13)+QUOTIENT(F12-INDEX(G3:G8,F13),INDEX(E3:E8,F13))-1)&" of "&INDEX(A3:A8,F13)&" rods"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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)