Temporary Storage in IF function

[Guest]

I have a function like this..
=IF(expr > 0 , expr , " ")

Now if my expr is again a big formula.. then is there any way of avoiding
repeating it as the second argument.. ie any temporary variable for storage
is available? like this...

=IF ((a=long_expr) > 0 , a , "");

 

[Pete_UK]

If it is just one cell, then use a cell somewhere else to store the
value, eg put your expression in X1 then your formula can be:

=IF(X1>0,X1,"")

If you are doing this on a column of cells, then just copy the
expression down the X column (often referred to as a helper column),
then copy your formula down.

Hope this helps.

Pete

 

[Guest]

Unfortunately, you will have to have the formula stated twice, either within
the IF() function or somewhere else in the sheet.

However, if you are testing for an error, Excel 2007 has a new IfError()
function that allows you state your formula but once:

=iferror(long formula, value if formula errors)

If the formula works, IFERROR() gives the formula's results. If it errors,
IFERROR() returns the "value of formula errors"

 

[Guest]

You might also look at Laurent Longre's morefunc.xll add-in. The SETV and
GETV functions appear to be applicable to what you want to do.

http://xcell05.free.fr/

Or maybe you could just use
=MAX(expr, 0)
and use a custom number format like
0;-0;;
to suppress displaying the 0 if you do not want it displayed? The cell will
still have a value of 0 - so this may affect your dependent formulas.

 

[Ragdyer]

You might try using 2 cells.

Say A1 is
=expr

And, in the result cell:
=IF(A1>0,A1,"")