About algorithm behind excel formula

[qazzzlyt]

In above example, all VLOOKUPs are indentical, and all 4 formulas give the same result: 42000. In Formula 4, the same VLOOKUP is defined in Name Manager.

My question is, in each of these formulas, how many times does excel ACTUALLY calculate the VLOOKUP? One or twice?

If I am using FILTER/SUM or other formulas, is calculation rule the same?

Thanks.

[#2]

It is the number of times you have instructed Excel to calculate VLOOKUPs.

To make it more clear:
Once in cell E1, once in cell F1, twice in cell G1, twice in cell G3, once in cell G5, none in cell G7 (because there is no VLOOKUP function associated in the given instruction.

Since excel is a top to bottom and left to right processing program (if I am not wrong), upper left cell are processed first. However, excel is cellular and so calculates each cell independently. It does not store any value. Even when you use LET function and use it in another cell, it recalculates whatever you have defined in the LET function every time.

It is the reason why excel gets heavier and resourceful when too much is going on. Let's say you have a formula in cell A1 that gives a value. You are using this value by referring to calculate, let's say salary for 6000 entries. Excel would do the calculation in cell A1 a total of 6001 times.

To understand how excel formula is behaving, you can go to Formulas tab and hit 'Evaluate Formula'

[#3]

Thank you very much for the answer. This is exactly what I need.

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.

[#4 · Accepted solution]

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.

[#5]

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.