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

[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.

 

[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

 

[Pimamedic]

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

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

 

[jco]

Thanks - I couldn't find help on the syntax, but I get the idea.

 

[joeu2004]

Can anyone confirm that this is the same as using IF?

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.

 

[jco]

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