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.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
1			Total	18,425.50	Intact	Lost		oil rod break
2			Weight	15,965.00		2,460.50
3	380		Number		351.21	28.79
4
5	Rods
6	Num	Wt
7	235	40.59	9,538.65	9,538.65	235.00	0.00	9,538.65
8	130	55.30	7,189.00	6,426.35	116.21	13.79	6,426.35	762.65
9	15	113.19	1,697.85	0.00	0.00	15.00	0	1,697.85
10	380		18,425.50	15,965.00	351.21	28.79	15,965.00	2,460.50
11
12
13
14
15			Brackets	Rate	Delta
16			0.00	0.0246366	0.024637
17			9,538.65	0.0180832	-0.00655
18			16,727.65	0.0088347	-0.00925
19			18,425.50
20
21			T10_1710a	1dd
1dd

Cell Formulas
Range	Formula
F2	=D1-D2
F3	=A3-E3
F7	=A7-E7
A3	=SUM(A7:A9)
E3	= SUMPRODUCT(--(D2>C16:C18),D2-C16:C18,E16:E18)
E7	=D7/C7*A7
E16	=D16-N(D15)
E17	=D17-N(D16)
E18	=D18-N(D17)
C7	=A7*B7
C10	=SUM(C7:C9)
D7	=MIN($D$2-SUM(D$6:$D6),C7)
D16	=A7/C17
D17	=130/C8
D18	=15/C9
G7	=B7*E7