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.
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.
