I have a formula challenge

[mgmcdevitt]

I need a formula for excel that can do the following:

I am from a drilling company and we have to price drill bit
replacements into your bids. The number of feet before the bit needs to
be replaced will be given. Then from the total depth drilled we
calculate the number of bits needed and round that number up.

I need to calculate the number of hours required to remove the drill
bits after each of them is unusable.

For example: Total footage to drill 2000’; bit footage before
replacement 500’, which is 4 bits. I am assuming it takes 100ft/hr to
remove the drill pipe with the drill bit.

Bit Depth Hrs
1 500 5
2 1000 10
3 1500 15
4 2000 20
Totals Hrs = 50

How do I use an excel formula to calculate total hours required to
remove all of the bits with just the 100ft/hr, # of bits, and footage
before bit replacement?

 

[Leith Ross]

Hello mgmcdevitt,

You can use 2 formulas to do the job. One to calculate hours and one to
sum the hours.


Code:
--------------------

A B C Formula for C
1 Bit # Depth Hours
2 1 500 5 =B1/100
3 2 1000 10 =B2/100
4 3 1500 15 =B3/100
5 4 2000 20 =B4/100
6 Total =SUM(C2:C5)

 

[Guest]

Hi,
If N=Number of bits then

N*(N+1)/2 * 500/100 = Number of hours

In your example: 4 *(4+1)/2 *500/100= 50 hours

Or more generally

N*(N+1)/2* Footage per bit/Removal rate per hour

So if a1=N, B1=Footage per bit, c1=Removal rate per hour then Hours (say) in
D1

= A1*(A1+1)/2*(B1/C1)