Robert is an associate at London's City law firm. He receives an Excel workbook, which he wants to review, before meeting with a client. However, he just noticed a puzzling SUM formula.
Bill is a Business Analyst with the company. Robert has just sent him an email:
"Bill, can you please decipher this formula for me? It was found in an Excel file from a client that I was asked to review. It does seem to be calculating a simple sum, but it's hard to understand what they were trying to achieve with this complex syntax."
=SUM(INDIRECT(ADDRESS(ROW(),8)):INDIRECT(ADDRESS(ROW(),COLUMN()-1)))
Bill is a Business Analyst with the company. Robert has just sent him an email:
"Bill, can you please decipher this formula for me? It was found in an Excel file from a client that I was asked to review. It does seem to be calculating a simple sum, but it's hard to understand what they were trying to achieve with this complex syntax."
=SUM(INDIRECT(ADDRESS(ROW(),8)):INDIRECT(ADDRESS(ROW(),COLUMN()-1)))
Evaluate Formula dialog box
Bill is the undisputed Excel Guru in the Finance department. He is aware that understanding how a nested formula calculates the final result is difficult, because there are several intermediate calculations.
Initially, he describes the nested functions inside the formula:
Then, Bill asks Robert to use the Evaluate Formula dialog box, so he can see the different parts of a nested formula evaluated in the order the formula is calculated.
Bill is the undisputed Excel Guru in the Finance department. He is aware that understanding how a nested formula calculates the final result is difficult, because there are several intermediate calculations.
Initially, he describes the nested functions inside the formula:
- ROW() returns the row number of the current row - the row the formula is on. If you are on row 1 it returns 1.
- COLUMN() does the same for a column number (not letter) If you are in column M it returns 13.
- ADDRESS(1,13) returns the address of the cell at row 1 column 13,that is M1.
- INDIRECT uses the contents of the reference. If M1 contained a reference "A1" then it would return the contents of cell A1.
Then, Bill asks Robert to use the Evaluate Formula dialog box, so he can see the different parts of a nested formula evaluated in the order the formula is calculated.
|
Steps showing formula evaluation
Note: The following functions are recalculated every time the worksheet changes and can cause the Evaluate Formula to give results different from what appears in the cell: RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, RANDBETWEEN, TODAY. |
|
Bill: Robert, all in all, its hard to say what the formula returns. If it was placed in cell M1, it would sum the range 'H1:L1'. It sums the same row, as the formula, from column 8 to the column one to the left of where the formula is entered.
Robert: Thanks Bill, you are my Excel hero. They are hundreds of formulas like this. I have a few more questions:
Robert: Thanks Bill, you are my Excel hero. They are hundreds of formulas like this. I have a few more questions:
- I plan to delete a couple of columns with redundant data. Is it OK to shift columns to the left ?
- Will a shorter formula speed-up the workbook ? Recalculation seems slow on my legacy laptop.
Circular Reference Errors
Bill: Actually, this formula is not very robust at all. Deleting columns to the left of the formula will trigger an unintentional circular reference error. Let me explain what this pitfall is:
One of the best practice spreadsheet modelling principles is that your formulas should be immune from movements around the grid of cells. Now take this formula and move to, let's say cell F1.
Excel will immediately display the circular reference warning below:
Bill: Actually, this formula is not very robust at all. Deleting columns to the left of the formula will trigger an unintentional circular reference error. Let me explain what this pitfall is:
One of the best practice spreadsheet modelling principles is that your formulas should be immune from movements around the grid of cells. Now take this formula and move to, let's say cell F1.
Excel will immediately display the circular reference warning below:
|
|
When a formula refers back to its own cell, either directly or indirectly, it is called a circular reference.
Note that the status bar displays the word 'Circular References' followed by a reference to one of the cells contained in the circular reference. If 'Circular References' appears without a cell reference, the active worksheet does not contain the circular reference.
Such an error is catastrophic, because Microsoft Excel cannot automatically calculate any of the open workbooks, when one of them contains a circular reference.
Note that the status bar displays the word 'Circular References' followed by a reference to one of the cells contained in the circular reference. If 'Circular References' appears without a cell reference, the active worksheet does not contain the circular reference.
Such an error is catastrophic, because Microsoft Excel cannot automatically calculate any of the open workbooks, when one of them contains a circular reference.
|
Now, try to use the Evaluate Formula dialog box once again. As you realize, the formula is summing the range E1:H1, which includes its own cell, F1 !
|
Assuming that the formula is in cell M1, it seems to me that it can be simplified to =SUM(H1:L1), which then can be safely copied down and across.
Volatile Functions
The slow workbook recalculation you mentioned, can be explained by the fact that INDIRECT is a volatile function.
Excel supports the concept of volatile functions, that is, functions whose value cannot be assumed to be the same from one moment to the next, even if none of its arguments (if it takes any) has changed.
Excel re-evaluates cells that contain volatile functions, together with all dependent cells, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation quite slow. I would advise you to use the following volatile functions sparingly:
The slow workbook recalculation you mentioned, can be explained by the fact that INDIRECT is a volatile function.
Excel supports the concept of volatile functions, that is, functions whose value cannot be assumed to be the same from one moment to the next, even if none of its arguments (if it takes any) has changed.
Excel re-evaluates cells that contain volatile functions, together with all dependent cells, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation quite slow. I would advise you to use the following volatile functions sparingly:
- NOW
- TODAY
- RAND
- OFFSET
- INDIRECT
- INFO (depending on its arguments)
- CELL (depending on its arguments)
The case study is presented in an effort to provide Excel practitioners with a way to apply conceptual material to real-world business problems across several sectors. We hope that you find the content engaging, dynamic and helpful in dealing with complex, realistic development problems in Microsoft Excel. While every effort has been made to provide an expert commentary from both academic and practitioner perspectives, the solution(s) presented may not be exhaustive. Please feel free to contribute your suggestion(s). All names and scenarios are fictional.
