Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

I'm trying to create an Excel VBA formula as follows:

I have a count of bolts that are always the same length on a given run.

Individual runs may contain alternate lengths, but the lengths will be identical for each run.

Example: Run 1 = qty 520 bolts at 25" each

Example: Run 2 = Qty 100 bolts at 14" each

Example: Run 3 = Qty 100 bolts at 22" each

I have two variables:

BoltQty

BoltLength

I need a formula that will calculate how many pieces of 96" threaded rod that I need to purchase for each run.

So for example, on Run #1, I need to figure out how many pieces of 96" threaded rod I will need to obtain to cut all qty 500, 25" long bolts

And on Run #2, I need to figure out how many pieces of 96" threaded rod I will need to obtain to cut all qty 100, 14" long bolts

The formula only needs to work per individual run, it does not have to cover any other run.

Any help would be appreciated; I'm hoping that someone might have a default formula that they use for situations like this

Thank you

I have a count of bolts that are always the same length on a given run.

Individual runs may contain alternate lengths, but the lengths will be identical for each run.

Example: Run 1 = qty 520 bolts at 25" each

Example: Run 2 = Qty 100 bolts at 14" each

Example: Run 3 = Qty 100 bolts at 22" each

I have two variables:

BoltQty

BoltLength

I need a formula that will calculate how many pieces of 96" threaded rod that I need to purchase for each run.

So for example, on Run #1, I need to figure out how many pieces of 96" threaded rod I will need to obtain to cut all qty 500, 25" long bolts

And on Run #2, I need to figure out how many pieces of 96" threaded rod I will need to obtain to cut all qty 100, 14" long bolts

The formula only needs to work per individual run, it does not have to cover any other run.

Any help would be appreciated; I'm hoping that someone might have a default formula that they use for situations like this

Thank you

96/25 = 3.84 ==> 3 Round down. Throw away the remainder. Yes this is a lot of waste.

96/14 = 6

96/22 = 4

You need normal division and round up for number of rods:

520/3 = 173.3 ==> 174 and so on

```
Function RodsNeeded(rQty As Range, rLen As Range) As Integer
RodsNeeded = rQty * rLen / 96
End Function
```

Usage: (assumes the quantity is in column "A" and the length in column "B")

=RodsNeeded(A2, B2)

=ROUNDUP(A2*B2/96,0)

Using your first example (qty 520 bolts at 25" each) you'd need

520 * 25 = 13,000 inches

13,000 / 96 = 135.416...

Rounded up = 136 rods

Thank you so much for the reply

Maybe I'm missing something but the formula doesn't work for me:

((Qty * Length) /96))

Using this example:

Qty 8 bolt sections @ 49" length each

RodsNeeded = ((8 * 49) / 96)

8 * 49 = 392

392/96 = 4.083

However, as I see it, this is incorrect. Since the length of the bolt is greater than 1/2 of the 96" threaded rod, (49 x 2 = 98) I would only be able to get qty 04 bolts (as opposed to 08 needed) out of the 04 rods total generated by the formula.

8 * 49 = 392 inches

392 / 96 = 4.08 rods

Rounded up = 5 rods

You can not work with the total length, you have to get the pieces per rod first.You are correct! I didn't think about that, but your formula given 8 pieces of length 49 returns 0.

```
Function RodsNeeded(rQty As Range, rLen As Range) As Integer
Dim intBoltsPerRod As Integer
intBoltsPerRod = 96 \ rLen
RodsNeeded = rQty * intBoltsPerRod
End Function
```

I am using straight up VBA in a userform---no cells involved, only variables---so I was unable to test "Roundup" & "RoundDown" I will check for the equivalent VBA functions and test d-glitch's formula

I am using straight up VBA in a userform---no cells involved, only variables---so I was unable to test "Roundup" & "RoundDown" I will check for the equivalent VBA functions and test d-glitch's formula

Here is VBA code examples for CEILING and FLOOR functions:

http://www.tek-tips.com/faqs.cfm?fid=5031

http://www.tek-tips.com/faqs.cfm?fid=5031

```
Private Sub Test()
Const QTY = 8
Const LENGTH = 49
Dim intBoltsPerRod As Integer
intBoltsPerRod = 96 \ LENGTH
MsgBox QTY * intBoltsPerRod ' ==> 8
End Sub
```

BoltQty / (Int(96 / BoltLength)) 'Where INT is functioning as RoundDown

Some sample runs:

Dim BoltLength As Double

Dim BoltQty As Integer

Dim RodTest As Double

Test #1

BoltLength = 17

BoltQty = 10

RodTest = BoltQty / (Int(96 / BoltLength))

MsgBox RodTest "The value displayed was "2"

Test #2:

BoltLength = 33

BoltQty = 10

RodTest = BoltQty / (Int(96 / BoltLength))

MsgBox RodTest "The value displayed was "5"

Test #3:

BoltLength = 47

BoltQty = 12

RodTest = BoltQty / (Int(96 / BoltLength))

MsgBox RodTest MsgBox RodTest "The value displayed was "6"

Test #4:

BoltLength = 49

BoltQty = 7

RodTest = BoltQty / (Int(96 / BoltLength))

MsgBox RodTest "The value displayed was "7"

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.