-------------------------------------------------------------------
THE OFFICE LETTER
STANDARD EDITION
Tips, Tricks, Tools, and Techniques for Microsoft Office
Volume 4, Number 16 October 4, 2004
-------------------------------------------------------------------
IN THIS ISSUE
1) Excel: Mmult Does Multiplication Tables
2) Word: Changing Font Properties
3) Excel: Quick Range Names
4) Reader Challenge: Keeping Text to One Page
5) Review: ActiveDocs 4.1
Premium Edition Extra:
Excel: Counting Entries in a Date Range
-------------------------------------------------------------------
1) EXCEL: MMULT DOES MULTIPLICATION TABLES
-------------------------------------------------------------------
Excel does a great job of quick math, but you may not know some of
the shortcut formulas that can save you time. This week we look at
the mmult function.
Remember multiplication tables -- where each factor to be
multiplied was listed across and down, and to find the product you
found the intersection of the right row and column?
Businesses use such tables all the time. For example, suppose you
have a table of prices and quantities, such as the cell range
below:
Qty
Price 1 2 3 4 5
10
11
12
13
You've created the Price column and several Quantity columns. The
table now needs formulas to populate the inner cells to tell you
how much revenue you'd have if you sold the number of items for
each of the prices listed. For example, how much revenue would you
have if you sold 1 item for $10. (OK, we admit this is overly
simple, but simple examples are easier to follow -- and include in
this newsletter.)
You could, of course, create a multiplication formula in the first
cell (the intersection of Qty 1 and Price $10), then copy that cell
to the remaining cells. That would certainly work. There's
another way that works, too: using Excel's mmult function, which
stands for matrix multiplication. Don't worry -- it's not
arithmetic voodoo. It's quite simple.
Let's assume that the table begins in cell A1; "Price" is in cell
A2, the Qty "1" label is in cell B2, and the formulas should fill
cells B3 through F6. (See the online edition for the completed
example.)
To create a "multiplication table" of values for this example:
1. Select the cell range where you want the calculations to be
entered. In our example, select the cells from Qty of 1 and Price
of 10 to the bottom right corner -- Qty of 5 and Price of 13.
Select the cell range B3 through F6.
2. Enter the formula:
=MMULT(A3:A6,B2:F2)
and instead of pressing Enter, press Ctrl + Shift + Enter to create
an array formula.
Each cell is now the result of matrix multiplication -- the price
times the quantity. Change any value -- a price or quantity, for
example -- and the cells are updated, just as they would be for any
formula.
MARKUPS
Another use for such matrix multiplication is to compute retail
price based on wholesale price plus a markup.
For instance, instead of Qty in the previous example, suppose we
have markup percents: 5%, 8%, 10%, and 12%. When our example is
complete, we'll know how much to mark a price tag for a product we
bought for $10 and want to sell at a 5% markup.
Our online edition shows the result.
The table, which starts in cell A10, includes four columns of
percentages: .05, .08, .10, and .12. We've used the Percent
toolbar button to convert their display to percents (5%, 8%, etc.)
For the cell range, we've used this array formula:
=MMULT(A12:A15,1+(B11:E11))
We want to multiply the price column by the markup. Remember that
to compute the final price of the item, you need to add 1 to the
percent. That is, to compute the final sales price of a $10 at a
5% markup, you need to multiply 10 by 1.05. This explains why the
"1+" in the array formula. We want to multiply price (A12 through
A15) by the sum of 1.0 and the markup percent). Need to calculate
price tags for a different price or markup -- just change the
appropriate cell and the matrix multiplication updates the
calculated values automatically.
-- James E. Powell
-------------------------------------------------------------------
SPONSOR: SNAGIT SOLVESIT. TIME AND TIME AGAIN.
-------------------------------------------------------------------
More than seven million people already know: SnagIt is the software
utility that lets you capture and share anything on your screen.
Whether you're looking to get a logo into a presentation quickly
and easily, want to email a chart without sharing the whole Excel
file, or want to share an article without sending all those
annoying banner ads - SnagIt is the answer.
Let SnagIt save you time - try it free today!
http://www.techsmith.com/products/snagit/default.asp?BAC-Soffice
-------------------------------------------------------------------
2) WORD: CHANGING FONT PROPERTIES
-------------------------------------------------------------------
Works with Word 97 and above
Search and replace can be used for more than simply changing one
word or phrase into another. For example, if I'm creating a
business letter and I import a plain text document (or paste text
from the clipboard) that contains the phrase The Office Letter, I
want to format the name of our publication in italics -- an
industry style standard.
To apply the change throughout the document:
1. Use the Edit/Replace command (or the keyboard shortcut, Ctrl +
H).
2. Enter the phrase to look for; in this example, I enter The
Office Letter in the Find what box.
3. Enter the same word or phrase in the "Replace with:" box.
4. Click on the More button if Search Options aren't displayed at
the bottom of the Replace tab.
5. Click on the Format button and choose the Font option.
6. Set the font to Italics and click on OK.
Notice that under the Replace with field is a notation: Format:
Font: Italic. This tells Word that the replacement should be
formatted with italic text.
7. Click on the Replace All button to make the change to all
occurrences within the document.
-- James E. Powell
-------------------------------------------------------------------
EASY-TO-USE WEB HOSTING FROM $9.95
-------------------------------------------------------------------
All plans include 24/7 technical support, unlimited e-mail, state
of the art web site builder, control panel admin, FrontPage and ASP
support. Domain names just $12.95. No contracts required, and
we'll set you up for FREE in under 20 minutes. Sign up today at:
http://www.officeletter.com/thehostgroup.html
-------------------------------------------------------------------
3) EXCEL: QUICK RANGE NAMES
-------------------------------------------------------------------
A couple of readers alerted us to an oversight in last week's
discussion that included instructions for creating range names.
John Broughton explained it this way:
In The Office Letter of September 27, 2004, there is a section
on "SHORTCUT: CELL RANGE NAMES". What wasn't mentioned was
another quite quick way to name a range of cells. Select
(highlight) the cells, then type a name in the Name Box, and
press [Enter].
The Name Box John's referring to is just below the toolbar; it
typically contains the address of the selected cell.
Note that John's suggestion works well if you have only a couple of
range names to create. The directions we provided work best if you
have, say, 10 columns and you want to assign a different range name
to each column; our technique assigns the 10 names in one simple
command.
-------------------------------------------------------------------
SPONSOR: GET ORGANIZED WITH FEATURES OUTLOOK DOESN'T OFFER
-------------------------------------------------------------------
AMF Daily Planner & PIM includes a complete Rolodex(tm)-style contact
manager, a full calendar, and a sophisticated scheduling system for
your appointments and events. It even keeps track of your notes and
tasks. Its folder-organized system offers multiple data views,
powerful search, and easy import/export. Plus, unlike Outlook, you
don't need a server to add networked users. Get more information
here:
http://www.amfsoftware.com/affilpiml.pl?affil+10057
-------------------------------------------------------------------
4) READER CHALLENGE: KEEPING TEXT TO ONE PAGE
-------------------------------------------------------------------
Mauricio Mathov wrote with this question for TOL readers:
Regarding section 3 (The Perfect Document) in the [last week's]
Office Letter. I am using the second method: I created pages 1
and 2 of the document, each with different headers/footers, and
then I use them to prepare other documents which I (usually)
save with "Save As".
My "perfect document" was created defining the first page as
"Section 1" and the second page as "Section 2."
The problem is that when I start typing in page 1 and I exceed
the available space, Word creates automatically a second page
that still belongs to Section 1 (and therefore has the Section 1
heading/footer) instead of using the already existent Section 2
for the text that did not fit in Section 1.
To solve this, I have to manually cut the text that does not fit
page 1 and paste it in Section 2 (i.e. my page 2). Later on, if
I have to edit page 1, the problem re-appears.
Is there any way to keep typing with Word realizing that once
page 1 is full it has to continue using page 2 in the next
Section? Would a template work better?
Thank you very much.
We couldn't find a way to restrict Section 1 to a single page. If
you know of a way, please let us know. Write to:
[EMAIL PROTECTED]
-------------------------------------------------------------------
5) REVIEW: ACTIVEDOCS 4.1
-------------------------------------------------------------------
If you prepare repetitive documents - where more of your time is
spent finding and replacing than composing - then a document
automation utility can save you time and relieve the stress caused
by having to make sure you've caught every little change.
We recently tested Keylogix's ActiveDocs (first license $599,
additional desktop licenses from $149; www.keylogix.com) and were
pleased the sophisticated replacement and validation options it
offered. The downside: it comes with a steep learning curve, and a
tutorial that needs a serious reorganization - putting complex
terms and procedures ahead of a simple example that would help a
user get the hang of the product. I found myself fighting to
understand ActiveDocs, not embracing it right away.
The first step in the document-creation process is to create the
document template, which will be used as the basis for other
documents. You define the fields, whether each is required, and
any restrictions (a list of valid values or a list of smallest and
largest allowable values, for example). (See illustration in our
online edition this week.) The program's sophistication is evident
from the start - the list of valid values can be entered manually
(and is thus relatively static) or pulled from a database via ODBC
or OLE-DB. Some fields can be filled automatically. For example,
in a Payment Overdue letter to a customer, you can include a
"please pay by" date that is 7 days from the current date;
ActiveDocs does the calculation for you.
If an item repeats, ActiveDocs can handle it. For example, if you
have a long document and want to include one page for each employee
in your department, you simply tell ActiveDocs about the repetition
and give it the employee name and the program does all the work in
generating the pages.
Rules are another way to handle complex documents: you can tell
ActiveDocs to include (or exclude) a portion of a document based on
a user's answer to a prompt. For example, if you instruct
ActiveDocs to ask for the payment method and user clicks the Cash
radio button, ActiveDocs won't build text about credit terms and
payment schedules into the contract document you build.
You don't have to start from scratch each time you build a new
template. ActiveDocs can import field definitions from another
template (such as those you put into a shared folder on your
network).
The Clippings Manager lets you reuse bits of text from other
documents by storing them in a directory. But it isn't just
content you can reuse. ActiveDocs has the ability to take field
data you've entered (a contact's name and the amount due, for
example) and use those answers when creating a different document
(assuming the field names match). It's a great way to put all your
answers in one file, then use them to generate a variety of
documents at different times.
If you don't need all this sophistication, you might be better off
with DataPrompter, which we've reviewed earlier this year
(http://www.officeletter.com/blink/dataprompter.html).
DataPrompter has fewer of the advanced features (it can't do date
math, for example), but it also isn't constrained to using document
templates, an approach that can get tiresome for quick-and-dirty
projects. For example, I have a document that I needed to set up
once so that I could replace key fields, print the document, and
then exit. No need to save the document - I just need to quickly
replace the same fields each time I need a new printed document.
With DataPrompter I can apply fill-in-the-field logic to any
document, then invoke the program, enter the values, and print my
document. With ActiveDocs I must first create a template, then
turn around and create a new document based on that template.
While changes to values used in the preparation of a document are
easy to make, it's the "create a template, exit, create a new
document" steps that are tedious. DataPrompter also is easier to
use out of the box - there's a LOT of terms to master in
ActiveDocs, which makes it a poor choice for the casual user.
There are several versions of ActiveDocs; you'll need at least a
single full license (at $599) to get started, then $149 desktop
edition for additional PCs within your organization. A $249
desktop edition includes Active Clippings that lets you access your
clippings from a Task Pane (though this is only supported in Office
2003).
For more information, including an overview of features, visit
http://www.keylogix.com.
-- James E. Powell
-------------------------------------------------------------------
PREMIUM EDITION EXTRAS THIS WEEK
-------------------------------------------------------------------
This week Premium Edition subscribers are learning how to count the
number of rows in which a cell contains a value within a user-
defined range.
Why not subscribe to the Premium Edition today for just $12/year?
HTML format, extra content, access to all back issues, a fast
search engine, a printer-friendly format option, and no
advertising! Visit:
http://www.officeletter.com/subscribe.html
-------------------------------------------------------------------
CONTACT POINTS
-------------------------------------------------------------------
SUBSCRIPTION CHANGES
Your subscription record shows the following:
Email address: [EMAIL PROTECTED]
First name: subscriber at [EMAIL PROTECTED]
Last name:
Full name:
If you would like to edit the above data, click here:
http://www.office-letter.com/cgi-bin/mmp/[EMAIL PROTECTED]
To unsubscribe, visit:
http://www.officeletter.com/sub/substdremove.html
and enter your e-mail address.
SHARE YOUR TIPS: Send them to [EMAIL PROTECTED] and include
your permission to acknowledge you by name.
FEEDBACK: Send your suggestions and comments about The Office
Letter to [EMAIL PROTECTED]
RECOMMEND THE OFFICE LETTER TO YOUR FRIENDS AND COLLEAGUES
Point them to http://www.officeletter.com/current.html
MORE FREE TECH E-NEWSLETTERS: http://www.techletters.com
----------- THE OFFICE LETTER ----- www.officeletter.com ----------
Tips and Tricks for Microsoft Office - Published Weekly
Copyright 2004 Masterware, Inc. All rights reserved
Now In Our Fourth Year - ISSN: 1543-5768
Editor in Chief: James E. Powell
Contributing Editors: Jim Boyce (www.boyce.us)
Dick Archer (www.diseno.com)
