Formula for adding weight based on a greater than scenario

[PatrickThorne]

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.

 

[FormR]

Hi, the description is a little confusing - but maybe something like this:

Excel 2013/2016
	A	B
1	Book Qty	Total Weight
2	150	115.5
Sheet1

Cell Formulas
Range	Formula
B2	=(A2*0.75)+(ROUNDUP(A2/50,0)*1)

 

[PatrickThorne]

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!

 

[PatrickThorne]

Nevermind. I figured this part out.

Thanks again for your help!