Formula for adding weight based on a greater than scenario

PatrickThorne

New Member
Joined
Jul 31, 2019
Messages
3
The printing company I work for is running into a lot of manual entry on excel to figure out shipping weights for freight and customer cost.

A quick example would be:

Book Quantity = 150
Book Weight (per unit) = .75lbs

A carton for the books weighs 1lb each. A carton can fit a finite amount of books. Currently, we have someone going in to manually multiply the book quantity by book weight, then determining how many cartons the job will take.

150*.75lbs=112.5lbs
One carton will hold 50 books. So 150/50=3 cartons.
112.5lbs+3lbs=115.5lbs

My question is, would there be a way to have a formula that would autofill in cells something along the lines of...

150*.75+(1<50, 2>50<100, 3>100<150) or something similar.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi, the description is a little confusing - but maybe something like this:


Excel 2013/2016
AB
1Book QtyTotal Weight
2150115.5
Sheet1
Cell Formulas
RangeFormula
B2=(A2*0.75)+(ROUNDUP(A2/50,0)*1)
 
Upvote 0
That worked wonderfully. That's exactly what I was looking for. Another, probably stupid, question I have is how would I get the column with the now calculated weight to always round up to nearest whole number?

So, for example, if I have a weight of 115.25lbs, how could I get that to autofill as 116lbs?

Thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top