Is it possible to avoid re-evaluations in subsequent formula parameter(s) if an evaluation has already been conducted in an earlier parameter?

[Rnkhch]

Hello,

This is a general question which I recently started to think about as the size of several of my files are growing rapidly do to huge amounts of data generated by students.

For example, take this simple formula: IF(AVERAGE(A1:C1)>2,AVERAGE(A1:C1),1)

Here, the compiler evaluates AVERAGE(A1:C1) in the first parameter to see if the condition is TRUE or not, and then it re-evaluates the same expression in the second parameter to get the value if TRUE. Of course, in simple cases like this, this is ok, but when there are thousands of complex formulas, then avoiding these re-evaluations will make a big difference in terms of speeding up the file. (right now, in some of my files the delay has reached a couple of seconds every time I make a change.)

I was hoping if there is any way to recycle evaluations from those parameters that have already been checked by the compiler? Thanks for any input!

 

[Dave Patton]

Current versions of Excel include the Let function; try it.

Let_a.xlsm
	A	B	C	D
1	2	4	6	4
2
3e

Cell Formulas
Range	Formula
D1	D1	=LET(a,AVERAGE(A1:C1),IF(a>2,a,1))

 

[Rnkhch]

Wow! Yes, that's a phenomenal suggestion!! I'll try it during the coming week, and I feel confident it should help. Thank you. I'll update.

 

[Dave Patton]

Thanks for the feedback.

 

[Rnkhch]

It is working really well, and I can already feel the benefit of LET-based coding. Thanks a gain for the suggestion!

 

[jaeiow]

Do you think Excel actually calculates twice? I was always told it only calculates when needed, and assumed it was smart enough to know that an equation is equal to an equation

 

[Fluff]

With the formula the OP posted this part AVERAGE(A1:C1) will calculate twice.

 

[jaeiow]

I've not heard that using LET() represents a performance improvement, only in readability. I will believe you once I know more.

 

[Fluff]

I've not heard that using LET() represents a performance improvement

That's the whole point of it, you only need to calculate something once rather than multiple times.

 

[jaeiow]

You are correct. Of course, this second part of the OP quesition, of having early cutoff for proofs, is not possible, except maybe with Lambda?