In message of Sun, 18 Aug 2013 08:39:21 in
microsoft.public.excel.worksheet.functions, joeu2004
writes
"Walter Briscoe" wrote:
One thought is the IF function, but that does not do lazy
evaluation. e.g. =IF(True,Truepart,Falsepart) calculates
both Truepart and Falsepart.

That would be correrct if IF() behaved like a real function. For
example, the problem with AND(ISNUMBER(B1),B1*B2B3) is that AND() does
indeed calculate all of its parameter, even if the a left-hand
parameter is FALSE (e.g. B1 is text), making it unnecessary to
calculate parameters to the right.

However, IF() behaves like an operator whose syntax looks like a
function. In this case, it calculates its parameters left-to-right only
as needed. We rely on this "all the time". For example, IF(ISNUMBER(B
1),IF(B1*B2B3,1,2),3) avoids the #VALUE we get with AND() when B1 is
not numeric.

Some other worksheet "functions" work the same way, notably CHOOSE().
CHOOSE() calculates only its first parameter and the parameter selected
by it.

Thanks to both joeu2004 and Claus for your prompt replies.
I was wrong and you are both right about the operation of IF.
I was thinking of the VBA function IIF.
I did step through the calculations of IF and
IF(logical_test,value_if_true,value_if_false)
value_if_true is only evaluated if logical_test is TRUE.
This is different from the VBA function
IIf(expr, truepart, falsepart).

I changed my expression to refer to fixed columns. i.e. to
<=INT(--($A2<$A1)*VLOOKUP($F2,Stations,38,TRUE)+--($A2<$A3)*VLOOKUP($G2,Stations,38,TRUE))
I timed the copy through at 13 minutes and 24 seconds.

I put Claus's idea in the next column and copied through.
<=INT(IF($A2<$A1,VLOOKUP($F2,Stations,38,TRUE))+I F($A2<$A3,VLOOKUP($G2,Stations,38,TRUE)))
I was surprised the 2 formulae copied through in the same time. ;)

I am surprised.

I tried =countif(N2:N2899,0), but Excel seemed not to like countif on
furmulae resulting in 0.

I copied the data and pasted the values and found 1942 of the values
were 0. i.e. I would expect a saving of at least 70%.
Plainly I don't understand what is going on. ;(
--
Walter Briscoe