Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 60 Add Descriptive Text to Your Formulas

Excel's named ranges and cell
comments can help clarify formulas, but sometimes you want to put
documentation into the formula itself. With the hacks in this
section, you can add descriptive text to your formulas quickly and
easily.

Even when you have written various
formulas and functions yourself, coming back to them at a later date
often requires that you follow cell references to try to figure out
what the formulas are doing. It would be great if you could simply
add to the end of your formula some text that
wouldn't interfere with the result, but would give
you the information you require at a later stage.

The problem, of course, is that the moment you add or incorporate
text into part of a formula, the result will no longer be numeric and
cannot be used in further calculations. Excel does, however, provide
one often-overlooked function that you can use to add descriptive
text to formulas or functions.

Say you have the following formula in cell A11:

=SUM($A$1:$A$10)*$B$1

Assume $A$1:$A$10 houses various numeric results that represent
totals for a particular month, and $B$1 contains a percentage value
that represents a tax rate. You could add some descriptive text to
the formula using Excel's N
function:

=SUM($A$1:$A$10,N("Values for April"))*$B$1+N("Tax Rate for April")

Now
you can determine what the formula is being used for simply by
selecting this cell and looking in the Formula bar. The
N function always will return a value of
0 for any text, and so does not interfere with the
formula's result in any way.