Help on this formula =$E$36-(G51>0)*$E$20

J

jco

Can anyone confirm that this is the same as using IF? And where can I find
more info on how to use this form of consitional value.
 
P

Pete_UK

Your formula is equivalent to:

=IF(G51>0,$E$36-$E$20,$E$36)

Have you tried looking in Excel Help for Boolean or IF ?

Hope this helps.

Pete
 
P

Pimamedic

=if(g5>0,($E$36-g51)*$E$20,""

Pete_UK said:
Your formula is equivalent to:

=IF(G51>0,$E$36-$E$20,$E$36)

Have you tried looking in Excel Help for Boolean or IF ?

Hope this helps.

Pete
Click to expand...
 
J

joeu2004

Can anyone confirm that this is the same as using IF?
Click to expand...

Where "this" is the expression in your Subject line, namely:

=E36 - (G51>0)*E20

(In the future, please put everything in the body of your posting,
even if it duplicates your Subject line.)

Yes, that is logically equivalent to both of the following:

=E36 - if(G51>0, E20, 0)

=if(G51>0, E36 - E20, E36)

However there is a subtle difference. In the first form (without
"if"), the "E20" subexpression is always evaluated. In the second two
forms (with "if"), the "E20" subexpression is evaluated only if the
condition (G51>0) is true.

I believe this makes a difference only if "E20" were complicated time-
consuming subexpression, for example one that calls a lookup function
or a complicated user-defined function.

The advantage of the first form (without "if") is that it is one less
nested function call. This is significant for Excel revisions before
2007, which have a limit of 7 nested calls.
 
J

jco

Thanks joeu2004! Most helpful. And thanks for the tip on including everything
in the body of the message.
 
Top