In the IF function formula, a cell address is placed where a logical test should be placed.

Occasional Visitor

We learned that the IF function consists of three parts (logical test, value if true, value if false) and is usually written as =IF(B2="Fragile",75,0).  But now I see someone wrote the IF function as follows

=IF(G40,G31*1000*0.7/G40,0) The first part is not a logical test, just a range of cells. 
What is the intent of this? How does it work?

1 Reply

@Kitanoteioh  wrote: ``=IF(G40,G31*1000*0.7/G40,0) [....] How does it work?``


It is equivalent to IF(G40<>0, G31*1000*0.7/G40, 0)


The purpose is to avoid a #DIV/0 error when G40=0.


We can write simply IF(G40, ...) because that is interpreted as IF("G40 is TRUE", ...).  In Excel any non-zero numeric value is interpreted as TRUE, and a zero numeric value is interpreted as FALSE in this context.


Be that as it may, I would prefer to write IF(G40<>0, ...) because that makes our intent clear.