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.
All-Stud-Pricing-Calculator.xls
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(B2*B3>120,1,(B2*B3)/14
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.
Copy-of-All-Stud-Pricing-Calcula.xls