Most people are familar with Excel's basic statistical functions: SUM, AVERAGE, MAX, MIN, COUNT and possibly COUNTA. I will demonstrate the use of some more functions:
INDIRECT, ADDRESS, ROW, MATCH, OFFSET, COUNTA
The resultant formulas themselves are over-elaborate, and normally unnecessary. All they do is double the value of a Salary column. In reality, all you would need is =2*D2 and copy this formula down column I. Their purpose is to demonstrate the use of the additional functions listed above.
History
Evaluating
Click on a formula, go to the Formulas tab, Formula Auditing group and choose Evaluate Formula. From here you can step through the evaluation of the formula.
You can also press the F2 function key to edit a formula, then carefully select part of the formula and press F9 to evaluate its result. You need to select a complete function or expression, so don't select an additional bracket or comma by mistake. Also be careful to then press Escape, rather than Enter, so that you abandon this evaluation, leaving the formula untouched.
This screenshot shows the result of using F9 with the MATCH function selected:
The First Formula
=2*(INDIRECT(ADDRESS(ROW(),MATCH("Salary",$A$1:$H$1,0))))
In summary, the word "Salary" is located in the header-row. Its column-number is combined with the current row's number to create a formula which doubles the current person's salary.
MATCH("Salary",$A$1:$H$1,0) MATCH will look for the word "Salary" in the range A1:H1. 0 says to look for an 'exact match'; that is, not to stop looking if it encounters a phrase similar to "Salary". MATCH returns the position of the match within the range. It will find "Salary" at the fourth position (within the range) so returns the value 4.
The dollar signs within the range create a fixed reference. This means that when we copy the formula down the column it will continue to refer to the range A1:H1, not changing to A2:H2, A3:H3, etc.. When building, or editing, the formula you can use the F4 function key to apply the dollar signs.
ADDRESS(ROW(),4) ADDRESS(ROW(I2),4) would be equivalent to ADDRESS(2,4) would would return "D2", the address of a cell based on its row and column index. Without a supplied value (an argument) ROW() returns the row number of the current row. That is, of the row that contains the ROW() function. This is very useful because dragging the formula down it will always identify the current row, so provides, effectively, a relative-reference formula.
Note that ROW() and COLUMN() are, I believe, examples of volatile functions; functions that recalculate whenever the worksheet is changed. They can affect performance.
Volatile Excel Functions
10 ways to improve Excel performance
ADDRESS(ROW(),4) For the first formula (in I2) this is equivalent to ADDRESS(2,4), which is "D2" (a textual expression, a string).
INDIRECT("D2") This converts the textual "D2" into a cell-reference. Finally, D2 is multiplied by 2.
The Second Formula
=2*(INDIRECT(ADDRESS(ROW(),MATCH("Salary",OFFSET($A$1,0,0,1,COUNTA($1:$1)-1),0))))
The slight difference here is the use of OFFSET and COUNTA rather than the specific range A1:H1. Assume, for the sake of argument, that we don't know how many columns our table might span. We need to look for the word "Salary" across however many columns our table occupies.
N.B. There is nothing particularly wrong with just using the entire row 1:1 in the formula. Excel is clever enough to realise that most of the row is unused. In fact, I'm using 1:1 in the formula anyway! Nevertheless, our formula demonstrates the functions COUNTA and OFFSET.
COUNTA($1:$1)-1 COUNT would count how many cells contain numbers, COUNTA counts how many cells contain text or numbers, or anything else. See it as count-all. Excel's description is "Counts the number of cells in a range that are not empty".
Be careful not to enter anything else in the first row!
COUNTA($1:$1)-1 gives 9-1 = 8. I'm subtracting 1 as there is no need look for the word "Salary" in the current (new) column's heading. So we now have:
OFFSET($A$1,0,0,1,8) OFFSET returns a reference to a range. The range starts at A1, spans 0 rows and 0 columns, and has height 1 and width 8. So we have A1:H1. In fact, because the value 8, and the other values, won't change, we have $A$1:$H$1. So now we have:
MATCH("Salary",$A$1:$H$1,0) This is now the same as the first formula, except that it will account for our table having additional columns.
The Excel Gurus are very comfortable with the use of the functions I have demonstrated. Others that they commonly perform miracles with are:
LOOKUP, INDEX, SUMPRODUCT
In fact, using INDEX results in a simpler version of the formula:
=2*INDEX($A$1:$H$108,ROW(),MATCH("Salary",$A$1:$H$1,0))
It just didn't occur to me to use INDEX! A difference with this version though, is that we would first need to determine the full extent of the table. This also results in a less efficient formula.
INDIRECT, ADDRESS, ROW, MATCH, OFFSET, COUNTA
The resultant formulas themselves are over-elaborate, and normally unnecessary. All they do is double the value of a Salary column. In reality, all you would need is =2*D2 and copy this formula down column I. Their purpose is to demonstrate the use of the additional functions listed above.
History
Spoiler
Evaluating
Click on a formula, go to the Formulas tab, Formula Auditing group and choose Evaluate Formula. From here you can step through the evaluation of the formula.
You can also press the F2 function key to edit a formula, then carefully select part of the formula and press F9 to evaluate its result. You need to select a complete function or expression, so don't select an additional bracket or comma by mistake. Also be careful to then press Escape, rather than Enter, so that you abandon this evaluation, leaving the formula untouched.
This screenshot shows the result of using F9 with the MATCH function selected:
The First Formula
=2*(INDIRECT(ADDRESS(ROW(),MATCH("Salary",$A$1:$H$1,0))))
In summary, the word "Salary" is located in the header-row. Its column-number is combined with the current row's number to create a formula which doubles the current person's salary.
MATCH("Salary",$A$1:$H$1,0) MATCH will look for the word "Salary" in the range A1:H1. 0 says to look for an 'exact match'; that is, not to stop looking if it encounters a phrase similar to "Salary". MATCH returns the position of the match within the range. It will find "Salary" at the fourth position (within the range) so returns the value 4.
The dollar signs within the range create a fixed reference. This means that when we copy the formula down the column it will continue to refer to the range A1:H1, not changing to A2:H2, A3:H3, etc.. When building, or editing, the formula you can use the F4 function key to apply the dollar signs.
ADDRESS(ROW(),4) ADDRESS(ROW(I2),4) would be equivalent to ADDRESS(2,4) would would return "D2", the address of a cell based on its row and column index. Without a supplied value (an argument) ROW() returns the row number of the current row. That is, of the row that contains the ROW() function. This is very useful because dragging the formula down it will always identify the current row, so provides, effectively, a relative-reference formula.
Note that ROW() and COLUMN() are, I believe, examples of volatile functions; functions that recalculate whenever the worksheet is changed. They can affect performance.
Volatile Excel Functions
10 ways to improve Excel performance
ADDRESS(ROW(),4) For the first formula (in I2) this is equivalent to ADDRESS(2,4), which is "D2" (a textual expression, a string).
INDIRECT("D2") This converts the textual "D2" into a cell-reference. Finally, D2 is multiplied by 2.
The Second Formula
=2*(INDIRECT(ADDRESS(ROW(),MATCH("Salary",OFFSET($A$1,0,0,1,COUNTA($1:$1)-1),0))))
The slight difference here is the use of OFFSET and COUNTA rather than the specific range A1:H1. Assume, for the sake of argument, that we don't know how many columns our table might span. We need to look for the word "Salary" across however many columns our table occupies.
N.B. There is nothing particularly wrong with just using the entire row 1:1 in the formula. Excel is clever enough to realise that most of the row is unused. In fact, I'm using 1:1 in the formula anyway! Nevertheless, our formula demonstrates the functions COUNTA and OFFSET.
COUNTA($1:$1)-1 COUNT would count how many cells contain numbers, COUNTA counts how many cells contain text or numbers, or anything else. See it as count-all. Excel's description is "Counts the number of cells in a range that are not empty".
Be careful not to enter anything else in the first row!
COUNTA($1:$1)-1 gives 9-1 = 8. I'm subtracting 1 as there is no need look for the word "Salary" in the current (new) column's heading. So we now have:
OFFSET($A$1,0,0,1,8) OFFSET returns a reference to a range. The range starts at A1, spans 0 rows and 0 columns, and has height 1 and width 8. So we have A1:H1. In fact, because the value 8, and the other values, won't change, we have $A$1:$H$1. So now we have:
MATCH("Salary",$A$1:$H$1,0) This is now the same as the first formula, except that it will account for our table having additional columns.
The Excel Gurus are very comfortable with the use of the functions I have demonstrated. Others that they commonly perform miracles with are:
LOOKUP, INDEX, SUMPRODUCT
In fact, using INDEX results in a simpler version of the formula:
=2*INDEX($A$1:$H$108,ROW(),MATCH("Salary",$A$1:$H$1,0))
It just didn't occur to me to use INDEX! A difference with this version though, is that we would first need to determine the full extent of the table. This also results in a less efficient formula.
0 Comments On This Entry
Trackbacks for this entry [ Trackback URL ]
← August 2020 →
S | M | T | W | T | F | S |
---|---|---|---|---|---|---|
1 | ||||||
2 | 3 | 4 | 5 | 6 | 7 | 8 |
9 | 10 | 11 | 12 | 13 | 14 | 15 |
16 | 17 | 18 | 19 | 20 | 21 | 22 |
23 | 24 | 25 | 26 | 27 | 28 | 29 |
30 | 31 |
Tags
My Blog Links
Recent Entries
Recent Comments
Search My Blog
0 user(s) viewing
0 Guests
0 member(s)
0 anonymous member(s)
0 member(s)
0 anonymous member(s)