It’s been a while since I posted anything. Here’s some training I did for my work recently:
Using Excel’s INDIRECT function
QUESTION: How can we find the average of X months starting with month Y and be able to change those variables without changing the formula?
Cell K6: =AVERAGE(INDIRECT(“R”&ROW(K6)&”C”&(I6+FirstMonthColLessOne)&”:”&”R”&ROW(K6)&”C”&(I6+FirstMonthColLessOne+J6-1),FALSE))
The AVERAGE function
AVERAGE(range of cells) like AVERAGE(D6:G6). D6:G6 is the reference (e.g., a range of cells). Since we can’t just put D6:G6 in a cell and use that inside AVERAGE(), we have to use the INDIRECT function so Excel can interpret D6:G6 as an address reference and not just text.
The INDIRECT function
The INDIRECT function returns the reference specified by a text string. The trick to this exercise was to create a formula that would dynamically create the text string. The formula ended up being:
The parameters for INDIRECT are (ref_text, [a1]). “ref_text” is just the formula above. The second parameter is a logical value that specifies what type of reference is contained in the cell ref_text. If it is TRUE or omitted, ref_text is interpreted as the normal A1-style reference. If FALSE, ref_text is interpreted as an R1C1-style reference, which is what we want so we can use the column number instead of the column letter.
Concatenation = Math with Text
Concatenation is the operation of linking text strings together or simply math with text. Some people use the CONCATENATE function, but that isn’t necessary. Just use the ampersand (&) and “add” text together. It works like a plus sign for text and is more intuitive.
Lets start with things to the left of the colon, “R”&ROW(K6)&”C”&(I6+FirstMonthColLessOne)
ROW(K6) = 6 and allows us to copy the formula and have the row automatically change
FirstMonthColLessOne is a named range for cell K1. A named range was used to make it easier to read the formula. This was purely option. To name a range, simple highlight the cell or range of cell and type the name in the little box in the top-left corner.
The value in I6 is 3 and FirstMonthColLessOne is 1, which total to 4, so “R”&ROW(K6)&”C”&(I6+FirstMonthColLessOne) is the same as R6C4 (row 6 column 4) or more familiarly D6 (D is the 4th column).
The right half of the formula is very similar. The trickiest part is (I6+FirstMonthColLessOne+J6-1) which is 3+1+5-1 or 8. The result is R6C8 (i.e., H6).
We add a colon in the middle using &”:”& and end up with R6C4:R6C8 which is the same as D6:H6.
Putting everything together we get:
AVERAGE(INDIRECT(“R6C4:R6C8”),FALSE)) which further evaluates to AVERAGE(R6C4:R6C8,FALSE))
We can now change the starting month and number of months by changing the values in cells I6 and J6.
The INDIRECT function can also be used where you want to use point a VLOOKUP to a range of cells, but that range may change from month to month.