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!

 

[Rnkhch]

I wonder if what is in the name manager is calculating for each cell

"what is in the name manager" will calculate once, and will be used for all cells that "call" for that name(s).

it results in a "{...}" and I presume is re-performing the formula

I wouldn't worry about the {...}.

But if you could show a specific example of what you're doing, then we can provide a better answer.

 

[jaeiow]

This formula

=LET(k,MaxNodes,r,1+QUOTIENT(k,(COUNTA(File_List[#Headers])-7)),
c,INDEX(File_List[[#Headers],[a]:[q]],IF(1+MOD(k,(COUNTA(File_List[#Headers])-7))=0,COUNTA(File_List[#Headers])-7,1+MOD(k,(COUNTA(File_List[#Headers])-7)))),

CONCATENATE(r,c))

where:
MaxNodes = SEQUENCE(COLUMNS(File_List[[a]:[q]])*MAX(File_List[Index.1]),1,0)

takes five seconds to calculate 16,000 rows (which I know is a lot of data to be generated in a dynamic array, but there should be some way to, if you alter the data in Headers on File List, it only update the dependencies and doesnt take six seconds to refresh). The good thing is that the data rarely gets that big.

 

[jaeiow]

It looks like parametizing speeds things up further:

=LET(m, (COUNTA(File_List[#Headers])-7), k,MaxNodes,r,1+QUOTIENT(k,m), l, 1+MOD(k,m),
c,INDEX(File_List[[#Headers],[a]:[q]],IF(l=0,m,l)),

CONCATENATE(r,c))

 

[Rnkhch]

From what I can see, yes, it's good that in the second formula you parametrized everything that is called for more than once. In my experience that has been generally helpful. In terms of how much the speed of your workbook refresh will improve, that depends on the complexity of the data and the size of the intermediate arrays. (For example, I have a file that refreshes in about one minute because the array sizes are upwards of 30,000 cells typically and several such arrays are involved in rather complex FILTERs.)

Now, for example, if this function is in turn called for by other functions in other cells, then you can define it as a name, and it will be only evaluated once for all those functions when the workbook refreshes.

 

[jaeiow]

=LET(m, (COUNTA(UnpivotHeaders)),o,  MaxNodes, r, 1+QUOTIENT(o,m), l, 1+MOD(o,m),
c,INDEX(UnpivotHeaders,IF(l=0,m,l)),

CONCATENATE(r,c))

where:

UnpivotHeaders: =File_List[[#Headers],[a]:[q]]

Now after working on it today to figure out how to dynamically change the amount of headersinluded. (File_List[[#Headers],:[h]] is normally the used area, while other columns are hidden and grouped/hidden), I came up with the Define Name,

VisibleHeaders

=FILTER(UnpivotHeaders,BYCOL(UnpivotHeaders,LAMBDA(l,IF(LARGE(CELL("width",INDEX('File List'!$A$1:$Y$1,1,XMATCH(l,File_List[#Headers]))),1)=0,"Hidden","Visible")))="Visible")

Idx

=LET(m, (COUNTA(VisibleHeaders)),o,  VisibleMaxNodes, r, 1+QUOTIENT(o,m), l, 1+MOD(o,m),
c,INDEX(VisibleHeaders,IF(l=0,m,l)),

CONCATENATE(r,c))

 

[jaeiow]

I finally got my unpivot scenario to under a second, to edit the source data. It took actually making the source data values only (from formulas), and doing some cleaning of the dependent array calculations, to parametrize within LET and remove unnecessary iferrors.

 

[Rnkhch]

Fabulous! Cheers