Round to foot?

Posted on 2012-09-05
Last Modified: 2012-09-05
I have a pricing calculator that I want to let my customer use to figure out how much a part will cost them.  The user can enter the quantity, size and length and in the background I want the price per piece to display.  I had everything working by rounding up and charging the customer for a whole bar of material (144") even if they only used say 10" of it.  That's not going to fly, so what I'd like to do is charge them for only the feet that's used up to the last two feet.  I don't want to calculate everything based on a 10 foot bar, only the last bar that they need.  

For example: (For simplicity, I have eliminated the saw blade thickness of .065 that gets added for each piece length in my formula)  if the user wants (20) pieces at 12" long, they would consume 240" or (2) 12 foot bars of material, and that's what I want them to be charged.  If the user wants (17) pieces at 12" long they would consume 204" and that's what I want them to be charged.  However, I don't want a lot of short bars of material ( < 2 feet) So if the user wants (22) pieces at 12" long, I want it to charge them for 24 feet, instead of of 22.....I want it to round the last bar up, so to speak.  

I can't figure out how to do this.
Question by:ITworks
    LVL 19

    Expert Comment

    by:Ken Butters

    on your "Calculation B7" worksheet....

    instead of a whole number of bars set to 1.... change it to calculate what portion of bars you are going to actually charge them for.

    You want to charge them if the left over is less than 24" (2 feet).

    using a bar length of 144" or 12 feet... then yhou want to charge them for 12 feet if they use more than 120".

    formula on row 7 of calcluation B7 worksheet would be:


    if the inches per piece times the number of pieces is greater than 120.... then the answer is 1 bar.

    if the inches per piece times the number of pieces is less than or equal to 120... then the answer is a fraction .... less than one... calculated by b2*b3/144.  or inches per piece times number of pieces / 144.
    LVL 4

    Author Comment

    But would this work if the customer order a lot of parts......where they would need 40 bars.
    LVL 44

    Expert Comment

    by:Martin Liss
    Are you aware that you can create your own formulas (called user defined formulas, or UDFs)?

    To do so type Alt+F11 or go to the Developer tab and click the 'Visual Basic' icon. Once there Insert a Module. You can then create a function like this one that would mimic your current  
    ROUNDUP((B2/B6),0) formula. To use it you would simply replace =ROUNDUP((B2/B6),0) with =BarsNeeded(). Now I know that that doesn't gain you anything but with some simple code you should be able to change this formula or create one to replace the formula you have in row 6.

    Public Function BarsNeeded() As Integer
        Dim intQty As Integer
        Dim intPcsPerBar As Integer
        BarsNeeded = Range("B2").Value / Range("B6").Value
    End Function

    Open in new window

    LVL 19

    Accepted Solution

    Updated formula to account for more pieces ordererd.


    if the bar uses more than 120 inches of last bar (determined by MOD.... if the amount needed divided by 144 is greater than 120... then you have less than 2 feet left ....   then use the next full bar.... otherwise use exact percentage of bars.
    LVL 4

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    761 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

    12 Experts available now in Live!

    Get 1:1 Help Now