TWO HANDY EXCEL HACKS
Calculating on the quick, plus fast formatting of
Calculating a Column
Excel provides a bunch of nifty calculation tools
that work on any group of number-holding cells
that has been selected. After making a selection,
the Status bar displays any one of the following
up a list of numbers and dates, the value will be
computed using both the date values — which are
calculated using a special set of rules — and the
ordinary numbers, and the result will be displayed
using the formatting of the first selected cell.
That adds up, alas, to a number that doesn’t
really mean anything. —Matthew MacDonald
Average. The average of all the selected numbers or dates.
Count. The number of selected cells (including
any cells with text in them).
Count Nums. The number of selected cells that
contain numbers or dates.
Min. The selected number or date with the small-est value (for dates this means the earliest date).
Max. The selected number or date with the largest value (for dates this means the latest date).
Convert Dates to
Dates imported from other programs frequently
cause problems in Excel. Many people manually
retype them, but there are easier ways to solve
The figure below shows a date format you
might encounter after importing (column A).
As the figure shows, the formulas in column B
convert the data in column A to three results in
column C, all of which follow the U.S. date format
Sum. The sum of all selected numbers.
Date formats converted to valid dates (U.S. format).
To convert to the European date format, simply
swap the “MID,” “LEFT,” and “RIGHT” values so
that the day, rather than month, appears first.
The next figure shows the same approach at work,
except that the cells in column C were formatted
with the European date format of dd/mm/yy.
To choose one of these options, select all the cells
to be included in the calculation, right-click anywhere on the Status bar, and choose from the menu Date formats converted to valid dates (European format).
that appears. The default option is Sum, which adds
together all the selected cells. You’ll see the calculated value displayed on the Status bar.
Most of the Status bar calculations won’t work
properly if you select both date and numeric information. For example, when attempting to add
Hopefully, these nifty formulas will take some
of the stress out of working with imported dates.
—David and Raina Hawley
Excerpted from Excel Hacks, Copyright© 2004 O’Reilly Media.
152 Make: Volume 01