I was recently asked to give a 5-minute presentation on any Excel topic for a “Lunch and Learn” session.  Five minutes?!?!?  That is not much time.  And besides, the Internet is chock-a-block full of Excel productivity tips, so how could I narrow it down to just one tip?  Also, I knew very little about the audience except to assume they were professionals who were not daily users of Excel.  They’re not beginners, but they only use Excel occasionally.

I decided the one feature in Excel that most people could be using but aren’t, is Excel tables.  Here’s why you’ll want to use this feature:

• It is so simple to implement. Just hit Ctrl-T from the keyboard.  (Or use the “Format as Table” button from the Excel ribbon.)
• Automatically sizes itself to accommodate existing rows and columns (right up to the first fully blank row and blank column).
• Automatically expands to accommodate a new row or column when you type in cells adjacent to the existing table.
• Automatically formats your list of data using alternately shaded rows, and a contrasting header row, for easy reading and a pleasant look. (If you don’t like the shading, you can use the Ribbon to change to a different Table Style.)

• Automatically includes Auto-Filter features (shown as little triangles in each column header).
• Automatically uses meaningful cell reference names in formulas. Instead of the old cryptic A2 – B2 notation, your formulas will look like [EndTime]-[StartTime].
• Automatically assigns a range name to the table (you can either accept the default name or change it). The more you use formulas, the more you’ll appreciate range names!
• … and many more benefits too numerous to list here.

Pretty much the only time I don’t use Excel tables is for information that is not in a list format.  In other words, if it is not in a row-and-column group of related information, for example, some financial reports that use multiple subtotal rows.

Here are a couple of really good (and short!) introductions to how to use Excel tables:
Want to become a Data God? Learn Excel Data Tables
http://chandoo.org/wp/2009/09/10/data-tables/

Or if you prefer video, here’s 8.5 minutes by Chandoo:
Introduction to Excel Tables
http://chandoo.org/wp/excel-tutorial/using-tables/

And a very thorough, concise intro over at Contextures (also includes some video tutorials):
Creating an Excel Table
http://www.contextures.com/xlExcelTable01.html

Whenever I start on a new project, if it’s anything like past projects, it will be a safe bet that there is no written documentation for the Excel files the client is using.  The users will do their best to explain it, then it will be up to me to go under the hood and figure out the nitty-gritty of what’s there.

Here are two things I find useful right from the get-go:

The Eight Things You Should Always Check Whenever You Receive an Excel File
www.mbaexcel.com/excel/the-eight-things-you-should-always-check-whenever-you-receive-an-excel-file

RefTreeAnalyser: The Ultimate Formula Auditing Tool
(an Excel Add-in that is well worth the $35 price tag)
www.jkp-ads.com/reftreeanalyser.asp

Give them a try next time you find yourself taking over an Excel file that was developed by someone else!

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)

1. 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)
2. 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)
3. 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))