The Question (long version)
Here’s the question as posed by the client in his own words:
I want to use Excel to calculate the total postage needed to mail packages of stuff to customers. So if they buy one medium T-shirt (10.7 oz) one large T-shirt (13.2 oz) and one pair of socks (2 oz) that all goes into a mailer that weighs 0.5 oz, then I can see that for this order I’ll need postage for 26.4 oz.
The tricky part, since we’re dealing with postage, is that the sum total must ALWAYS round UP to the next full number, no matter how close — if a package weighs 5.1 ounces, you STILL have to round up to 6 oz.
Similarly (or maybe worse!?) once you reach 13.1 ounces it becomes 1 lb. If you reach 1 lb 1 oz, it becomes 2 lbs., etc.
The Question (my simplified version)
For 13.0 oz and under, round up to the nearest ounce, all else round up to the nearest pound. How can I calculate this in Excel?
Now do you see why “word problems” are among the most important things you can learn about math?
The Answer (just the formula please)
Assuming that the weight in ounces is in cell A1, otherwise adjust accordingly:
=IF(A1<=13,ROUNDUP(A1,0),16*ROUNDUP(A1/16,0))
The Answer (including how we got there)
- First we work out the part that rounds up to the nearest ounce. You may already know of Excel’s ROUND function, but that rounds to the nearest number which could be either up or down. It has two related functions: ROUNDUP and ROUNDDOWN. For this problem we need this syntax:ROUNDUP ( Number, Num_digits )where
Number is the value to be rounded up
Num_digits is the number of decimal places to round the above number to.So in this case, where the weight in ounces is in cell A1, this is what we need for this part of our formula:ROUNDUP(A1, 0) - Now we need to calculate rounding ounces up to the nearest pound. Given that there are 16 ounces per pound, we have:ROUNDUP(16 * A1, 0)
- Finally, we need to combine those two parts and use #1 for weights less than or equal to 13 ounces, and #2 for everything larger. Here is the syntax we will use to combine:IF(logical_test, [value_if_true], [value_if_false])The logical_test that decides which calculation to use, is
A1<=13
which will evaluate to either true or false.
Putting it all together, we get:
=IF(A1<=13,ROUNDUP(A1,0),16*ROUNDUP(A1/16,0))