Does an if statement calculate both the true and false?

  • Thread starter Thread starter chriswilko
  • Start date Start date
C

chriswilko

I have a spreasheet, with 40000+ rows.
I have an if statement which is basically if(A1="OK",0,long and complex
formula).
Almost all instances are "OK" with a few exceptions requiring the complex,
time consuming part of the formula.
Does excel calculate that part of the formula even for cells which are "OK"
and hence taking up loads of time, or does it ignore it knowing it doesn't
need to return it?
Thanks!
Chris
 
Hi,


VBA evaluates both parts of the formula, the Excel worksheet IF function
only evaluates one part of the formula depending on whether the logical
condition is TRUE or FALSE


Tushar Mehta has an excellent tutorial on the IF function here

http://www.tushar-mehta.com/publish_train/xl_vba_cases/0110_the_excel_if_function.htm


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Excel does not appear to calculate the false part if the true part is valid.
I tried the formula:

=IF(A1<>0,0,trial(A1))
where trial is the UDF:

Function trial(r As Range) As Double
For i = 1 To 1000000
trial = trial + r.Value
Next
trial = trial + 1
End Function

I tried several numbers in A1 and the result was instantaneous unless I
entered 0
 
I have a spreasheet, with 40000+ rows.
I have an if statement which is basically if(A1="OK",0,long and complex
formula).
Almost all instances are "OK" with a few exceptions requiring the complex,
time consuming part of the formula.
Does excel calculate that part of the formula even for cells which are "OK"
and hence taking up loads of time, or does it ignore it knowing it doesn't
need to return it?
Thanks!
Chris
Click to expand...

Hi Chris,

If the value is "OK", it will check for the TRUE value and exit the
function... so no need to worry about the complex satement incase the
value is "OK"

Ratheesh
 
chriswilko said:
I have an if statement which is basically
if(A1="OK",0,long and complex formula). [....]
Does excel calculate that part of the formula
even for cells which are "OK" and hence taking
up loads of time, or does it ignore it knowing it
doesn't need to return it?
Click to expand...

Unlike a real function, the IF expression evaluates only the parts that are
necessary; at least, in Excel 2003. You can validate that fact for your
revision of Excel with the following.

=if(A1,myUDF(1),myUDF(2))

where A1 is a 0 or 1, and myUDF is:

Function myUDF(x)
myUDF = x
MsgBox "myUDF " & x
End Function


----- original message -----
 
Back
Top