Forum Discussion
About algorithm behind excel formula
In Case 1, yes, it would be 6001 time. Once for C1 and 6000 for the 6000 cells referring to C1
In Case 2, it would be 6000 times. The definition in the Name Manger would not be executed unless referred. So every time you refer to the Name SUMA1B1, excel checks what is SUMA1B1 and finds it to add A1 and B1. Same as Case 1, except C1 process is missing. Basically, referring SUMA1B1 only made the formula more readable. One other difference, instead referring C1 in the 6000 cells, you simply put A1+B1 in Case 2.In the Filter function, excel loads the entire range you may have defined, and runs the criteria throughout the array. And every time you refer to filtered value, this process is executed in the background.
Let me ask a follow up question if I could:
Case 1:
I write "=A1+B1" in C1, then refer to C1 in 6000 cells(once each cell).
So this workbook calculate "A1+B1" for 6001 times?
Case 2:
I define "A1+B1" in Name Manager as "SUMA1B1", then use "SUMA1B1" in 6000 different cells.
So this workbook only calculate "A1+B1" once?
I assume the rule can be applied to more complicated formulas/functions like FILTER, right?
Many Thanks.
In Case 1, yes, it would be 6001 time. Once for C1 and 6000 for the 6000 cells referring to C1
In Case 2, it would be 6000 times. The definition in the Name Manger would not be executed unless referred. So every time you refer to the Name SUMA1B1, excel checks what is SUMA1B1 and finds it to add A1 and B1. Same as Case 1, except C1 process is missing. Basically, referring SUMA1B1 only made the formula more readable. One other difference, instead referring C1 in the 6000 cells, you simply put A1+B1 in Case 2.
In the Filter function, excel loads the entire range you may have defined, and runs the criteria throughout the array. And every time you refer to filtered value, this process is executed in the background.
- Thanks again for the reply. I also just tested by defining NOW() in Name Manager, compared to write NOW() in each cell. To my surpise, using Name Manager is even slower, which supports your answer.