home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Media Share 9
/
MEDIASHARE_09.ISO
/
utility
/
pbaseiv.zip
/
P4SPR007.TIP
< prev
next >
Wrap
Text File
|
1991-12-16
|
2KB
|
48 lines
In a 1-2-3 worksheet, I have to calculate the difference
between two numbers as a percentage of the second one. I
used to do this with the formula (B12-C12)/C12. But if cell
C12 was empty, which it was if I hadn't yet typed a value,
the formula produced an ERR value. That didn't suit me; if
C12 was blank, I wanted the cell with the formula to be
blank as well.
To solve the problem, I replaced the formula with a new one,
@IF(@ISERR((B12-C12)/C12)," ",(B12-C12)/C12), which leaves
the formula cell blank if C12 is empty. The @ISERR function
evaluates the original formula and returns a true value if
the result is ERR. @IF puts a blank in the cell if @ISERR is
true or does the calculation again if @ISERR is false.
Robert Siegel
Vienna, Virginia
Editor's note: You can make this trick even easier by
placing the formula that might produce an error in a cell
that's outside the range you see and print. For instance, if
you put the formula that does the calculation in, say, AK12,
you can use the formula @IF(@ISERR(AK12)," ",AK12) . This
approach has two advantages: First, the math isn't done
twice; second, you can edit the formula once--rather than
twice--if it needs to be changed.
Postscript: Several readers responded to this tip by
claiming that the formula @IF(C12,(B11-C12)/C12,"") works
just as well as the more complicated one we printed. This is
only partially true. It catches divisions by zero, the most
likely error -- but it won't catch other errors, such as
overflows and underflows.
Siegel's method is much more versatile. Since the
@IFERR function can catch any condition that generates an
ERR, it will handle formulas involving logarithms,
exponentials, trigonometric functions, and date
functions--in which errors are harder to predict. Thus,
unless you're sure you can catch every error condition, it's
best to play safe and test for ERR instead.
Title: To ERR Is Electronic
Category: SPR
Issue date: Aug 1991
Editor: Brett Glass
Supplementary files: NONE