|
I
have always stated a strong case for using meaning range names in
formulae instead of cell references. Not only does it make the purpose
of the formula apparent. It also makes it a lot easier to troubleshoot
them. This structured form of working has a lot of benefits as clearly
described below.
This
tip
was provided by Bruce Judd, Strategic Decisions Group.
"We
use named variables in our Excel models almost exclusively. For
example, we'd write the equation for revenue:
MarketSize*MarketShare*AveragePrice rather than with
cell references: H12*K12*M12. These range names (MarketSize,
MarketShare and AveragePrice) are defined on an input sheet or in a
separate sheet where we are doing calculations. [We name the
variable with a lower-case first letter if on the input sheet (e.g.,
marketSize), or if it is a time-series of data (e.g., for years 2001,
2002, 2003...), we begin it with a capital letter.]
When we type an equation in for the first time in an equation, we always
type it in all lower-case letter. After you press Enter,
Excel looks for a definition of each variable. If the variable has
been defined, it converts it to the exact form of the definition (e.g.,
MarketShare, rather than marketshare). Using this trick, you can
look at an equation and easily see which variables, if any, are not yet
defined, because they have all lower case letters."
|
