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!

 

[Fluff]

I'm not sure what you are saying, there is no 2nd part to the OP's question.

 

[jaeiow]

"recycle evaluations from those parameters that have already been checked by the compiler" is not solved if you use the LET in multiple cells.

 

[Fluff]

That is not what the OP is talking about (IMO).

 

[Rnkhch]

Fluff is correct. I meant simply reusing parameter/code calculations within the same formula in the same cell while evaluating the cell, so that the parameter/code needs to only be evaluated once.

I've had an unbelievably pleasant experience with LET. As I was mentioning in my other thread a bit ago, one of my Excel applications which is very huge (249,820 cells with data and 229,620 formulas) used to take about 2-4 seconds to evaluate depending on how much data it contained. I redid all the formulas and used LET for portions that contained repeating occurrences of code. The result was unbelievable, and now my application evaluates in a split second like a small file

 

[jaeiow]

I also went through and LETted my formulas. It surprised me that Excel would redo the same calculations in a formula. I guess I still don't understand why they didn't implement that. But I have work to do with regard to getting some of the computations to run across the workbook, outside of the LET. I'm wondering if you know of any way to design that?

 

[Fluff]

The variables in the LET function are only available to that particular formula & that cannot be changed.

 

[jaeiow]

So would you put it in a helper cell if it's a computation that runs through the whole combination of cells, or is there an advanced technique?

 

[Fluff]

As I have absolutely no idea what you are trying to do, there is nothing I can do to help.
I would suggest that you start a thread of your own & clearly explain what you are doing.

 

[Rnkhch]

So would you put it in a helper cell if it's a computation that runs through the whole combination of cells

If I understand the question correctly, you can define names for code that needs to be utilized in multiple formulas in multiple cells. To do that, you can use the Name Manager. Then the code in that name gets evaluated per workbook evaluation. And the result of that one time of evaluation can be used in all other cells that refer to that name.

I have personally seen a lot of benefit in doing this as well.

But as Fluff mentioned, it's best to start a thread for your specific scenario.

 

[jaeiow]

I wonder if what is in the name manager is calculating for each cell. Every time i put a calculation, it results in a "{...}" and I presume is re-performing the formula. Before I would make a new thread, I want to be sure about it.