Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
About algorithm behind excel formula

- Home
- Microsoft Excel
- Excel
- About algorithm behind excel formula

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 02 2022 04:57 PM - edited Jan 02 2022 08:02 PM

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.

Labels:

- Tags:
- algorithm

4 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 02 2022 08:09 PM - edited Jan 02 2022 08:12 PM

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'

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 02 2022 08:31 PM

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.

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.

best response confirmed by
qazzzlyt* (Occasional Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 02 2022 09:00 PM - edited Jan 02 2022 09:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jan 02 2022 10:11 PM

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.