Round to foot?

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.
Who is Participating?
Ken ButtersConnect With a Mentor Commented:
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.
Ken ButtersCommented:

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.
ITworksAuthor Commented:
But would this work if the customer order a lot of parts......where they would need 40 bars.
Martin LissOlder than dirtCommented:
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

ITworksAuthor Commented:
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.