Solved

VBA Excel; Stumped on formula to variable measurements to fixed measurements

Posted on 2016-10-20
21
24 Views
Last Modified: 2016-10-25
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
0
Comment
Question by:Member_2_7966101
  • 8
  • 8
  • 5
21 Comments
 
LVL 27

Expert Comment

by:d-glitch
Comment Utility
You need to use integer division to find bolts per rod:
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
There could be a way to do it with a built in formula, but here's a user defined formula you can use. Place this in a module (like Module1).

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

Open in new window


Usage: (assumes the quantity is in column "A" and the length in column "B")
=RodsNeeded(A2, B2)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Try this instead

=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
0
 

Author Comment

by:Member_2_7966101
Comment Utility
To Martin Liss:

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.
0
 
LVL 27

Expert Comment

by:d-glitch
Comment Utility
Stealing some syntax

=ROUNDUP( A2* ROUNDDOWN (B2/96, 0), 0)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
The formula in post ID: 41852815 gives a result of 5

8 * 49 = 392 inches
392 / 96 = 4.08 rods
Rounded up = 5 rods
0
 
LVL 27

Expert Comment

by:d-glitch
Comment Utility
You can not work with the total length, you have to get the pieces per rod first.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:d-glitch
Comment Utility
=ROUNDUP( A2* ROUNDDOWN (96/B2, 0), 0)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Function RodsNeeded(rQty As Range, rLen As Range) As Integer

    Dim intBoltsPerRod As Integer
    intBoltsPerRod = 96 \ rLen
    RodsNeeded = rQty * intBoltsPerRod
End Function

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Member_2_7966101
Comment Utility
Rounding up will, I assume just bring me up to the next sequential rod which in tis case is "5" The formula should generate 8 rods using the above example

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
0
 

Author Comment

by:Member_2_7966101
Comment Utility
Rounding up will, I assume just bring me up to the next sequential rod which in tis case is "5" The formula should generate 8 rods using the above example

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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You can use my Function in post ID: 41852854 as "straight VBA".
0
 
LVL 27

Assisted Solution

by:d-glitch
d-glitch earned 250 total points
Comment Utility
Here is VBA code examples for CEILING and FLOOR functions:
     http://www.tek-tips.com/faqs.cfm?fid=5031
0
 

Author Comment

by:Member_2_7966101
Comment Utility
Thank you all. I have to leave my workstation.  FYI: In my testing of d-glitch's formulas I'm getting 16 rods as opposed to the needed 8. I will follow up here tomorrow.
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
Comment Utility
I think I finally got this right:(
Private Sub Test()
Const QTY = 8
Const LENGTH = 49
Dim intBoltsPerRod As Integer

    intBoltsPerRod = 96 \ LENGTH
    MsgBox QTY * intBoltsPerRod ' ==> 8
End Sub

Open in new window

0
 

Author Comment

by:Member_2_7966101
Comment Utility
Martin, That didn't quite work either (although maybe I'm not reading it correctly)  but I was lying down and it hit me. This seems to work:

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"
0
 

Accepted Solution

by:
Member_2_7966101 earned 0 total points
Comment Utility
I wanted to apportion the solution equally but the system wont let me
0
 

Author Comment

by:Member_2_7966101
Comment Utility
Thank you for the help.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've Requested Attention so that a moderator can help you assign points.
0
 

Author Closing Comment

by:Member_2_7966101
Comment Utility
I was able to come up with a solution on my own. I tried to apportion the solution equally but the system wouldn't let me
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now