Figure 471 A single TRANSPOSE function fills in these cells.

The advantage of using TRANSPOSE over using Paste Special, Transpose is that the TRANSPOSE func- tion is a live formula. If cells in column A change, they will change in row 2.

Additional Details: The example in this topic is a trivial example of merely copying the cells. In real life, you might need to do calculations instead of copying the data. You can use calculations with either the INDEX or TRANSPOSE functions. For example, the formula shown above squares the number and adds 1.

Figure 472 Do calculations while transposing.

SUM A RANGE THAT IS C5 ROWS TALL USING OFFSET

Problem: I need to add up a range that starts in cell A5 and is C5 rows tall. A formula in C5 is calculating a number and I need to include that many cells in the SUM.

Strategy: Use the versatile OFFSET function. OFFSET will let you: ●● Start from a specific cell or range of cells,

●● Move a certain number of rows from the starting position,

●● Move a certain number of columns from the starting position.

The first three arguments get you to a top left corner cell for the dynamic range. Note that if the starting cell is J10, you can point to J15 with a second argument of 5 or point to J5 with a second argument of -5. In a similar fashion, a third argument of 1 will point to K10. A third argument of -1 will point to I10.

Those first three arguments get you to a starting cell. In this current question, you don’t need any of that information, because you know that you are always starting from A5. Your formula will start with OFFSET(A5,0,0,…

OFFSET allows you to specify two more optional arguments.

●● The fourth argument describes the height of the dynamic range.

●● The fifth argument describes the width of the dynamic range.

You can use OFFSET(A5,0,0,C5,1) to return a range that is 1 column wide, and a variable number of rows tall.

Gotcha: In most cases, OFFSET will point to a range that is more than one cell tall. In these cases, you can not simply enter =OFFSET(). You have to use the OFFSET as an argument in another function.

In this case, use =SUM(OFFSET(A5,0,0,C5,1)). Change the 5 in C5 to a 3, and the formula sums A5:A7.

Figure 473 This formula sums A5:A9. Figure 474 Change C5 to a 3, and the sum range resizes.

Figure 475 Who needs OFFSET when =A4 works?

What happens when you delete row 4? The simple formula in column B changes to a #REF! error. The OFFSET formula in column E continues to work.

Figure 476 If rows might be deleted, OFFSET saves the day.

Additional Details: The starting range can be more than one cell. In the example that follows, the start- ing range is A4:A11. The third argument of the OFFSET function uses MONTH(A1) to move five columns to the right. This formula will total the column corresponding to the date in cell A1.

Figure 477 Use OFFSET to move a range n columns to the right.

Gotcha: OFFSET is a volatile function. This means that with every calculation of the worksheet, the OFFSET is recalculated, even if none of the cells in the table changed. Those cells could stay the same for a whole month, yet OFFSET will recalculate every time that you change a cell anywhere in this worksheet. Many OFFSET functions can cause your worksheet to slow down. In many cases, you can use INDEX instead.

Back in the VLOOKUP topics, you read how to use =INDEX( B4:M11,row,column) to return one cell from a range. If you leave out the row argument blank, Excel will return all of the rows. The formula of

=SUM(INDEX(B4:M11,,MONTH(A1))) will return an equivalent result.

Figure 478 Leave the Row argument of INDEX blank to return an entire column.

REPLACE VOLATILE OFFSET WITH INDEX

Problem: OFFSET is a volatile function. It breaks the smart recalc feature of Excel. A single OFFSET function near the top of the worksheet will cause every formula in the calculation chain to recalc constantly.

Strategy: There is a very unusual syntax of INDEX which replaces OFFSET. The advantage of INDEX is that the function is not volatile.

Normally, a formula such as =INDEX(A5:A11,C5) will look in C5 to find the number 4. INDEX will then return the 4th cell from A5:A11, or the 8 from cell A8:

Figure 479 The INDEX in C8 is the “normal” use of INDEX

However, when the INDEX function is adjacent to a colon, the behavior changes. The formula in C9 says to SUM from A5:INDEX(). If you use Evaluate Formula (see page 200) to watch the formula get calculated, you will see the INDEX function:

Figure 480 Immediately before evaluating the INDEX function

returns a cell address of $A$8:

2

Figure 481 Click Evaluate and the INDEX returns $A$8 instead of the 8 stored in A8.

You should be able to rewrite most OFFSET functions to use this form of INDEX. Your worksheets will calculate faster because INDEX is not volatile.

Additional Details: A few guys in Australia were working on a new add-in to audit formulas in work- sheets. Their add-in “stumbled” when it started uncovering this odd syntax of INDEX. As they tried to improve their add-in, they realized that INDEX had this seemingly undocumented behavior. Thanks to Joe McDaid and Dan Mayoh for passing this technique on to me at one of the ModelOff finals.

HOW CAN YOU TEST FOR VOLATILITY?

Problem: How do you know OFFSET is volatile and INDEX is not?

Strategy: Open a workbook. Make no changes. Close the workbook. Does Excel ask you to Save? If so, then something in the workbook is volatile.

Using this technique allowed Excel MVP Liam Bastick to discover that changing =SUMIF(A2:A100,”Yes”,B2:B100) to =SUMIF(A2:A100,”Yes”,B2) forces the SUMIF to become volatile.

If you use Conditional Formatting to turn cells red, the workbook is volatile. If you instead use a custom number format such as [Red][<6]0;0 you can change the font color to red without causing volatility.

WHATEVER HAPPENED TO THE @@ FUNCTION?

Problem: Back in Lotus 1-2-3, there was an @@ function. If you used @@(A3), Lotus would go to A3. A3 was supposed to contain a valid cell reference. Say that A3 contained the text C5. The @@ function would then return the value from cell C5.

Strategy: In Excel, this is called the INDIRECT function. Here are a few examples of how it works.

In the simplest case, consider a formula of =INDIRECT(F2). Excel will go to F2 and use the cell address found there. In the following figure, the answer in F4 first looks to F2 then to C1.