As we explained in our last two
articles, we begin in the cell reference and based on the rows moved argument
we move up or down (-2 means two rows up, 2 means two rows down, 0 means stay
in the current row.) Then based on the columns moved argument we move to the
left or right (-2 means move two columns to the left, 2 means move two columns
to the right, 0 means stay in the current column.) The cell we have now reached
is the upper-left corner of the array “picked off” by the OFFSET function. The
height argument gives the number of rows in the picked off array and the column
argument gives the number of columns in the picked off array. If the desired
array is a single cell, then the height and width arguments may be omitted.
In this month’s newsletter we first show how to use the OFFSET function to return the last number (even as data is added) in a column. Then we show how to use the OFFSET function to create dynamic ranges that update automatically when new data is added to a worksheet. Finally, we use the concept of a dynamic range to create a chart that always charts only the last 6 months of sales. All our work is in the file BeckerApril19.xlsx.
Returning the Last
Number in a Column.
In the worksheet Most recent (see Figure 1) Column B contains monthly unit sales of a product. We would like to write a formula that always (even when new data is added) returns unit sales during the most recent month. To accomplish this goal we entered into cell D4 the formula =OFFSET(B6,COUNT(B:B),0,1,1).
The key to this formula is the fact that COUNT(B:B) always returns a count of the number of numerical entries in Column B. Currently COUNT(B:B) = 7, so our formula moves 7 rows below cell B6 and returns the value of 110 from cell B13. If we add another month of sales to B14 (please try it!) then COUNT(B:B) = 8, so our formula will return the number in cell B14!
Figure 1 Extracting
sales during the most recent month
Creating a Dynamic
In the worksheet Dynamic Range (see Figure 2) we have sales of three products for each of our nine salespeople. We want to create a named range that automatically updates whenever new data is added beginning in column A and/or row 1. The key to creating a dynamic named range is to realize that from the Formulas Tab you can click on Define Name and create a range name based on a formula. It is important to get the dollar signs right in your formula, because Excel will “copy” the formula you enter as you move around the spreadsheet. To create the desired dynamic range click on Define Name and enter a name (we chose data) and in the Refers to portion of the dialog box enter (preferably by pointing to cells) the formula
This formula creates a range that always (because $A$1 is
dollar signed) starts in cell A1. We don’t move at all from cell A1. The COUNTA function counts all non-blanks in
a range, entire row, or entire column.
Here the COUNTA(‘Dynamic Range’!$A:$A) portion of the formula counts the
number of rows containing data in Column A. This gives us the desired height of
the dynamic range, The portion of the formula = COUNTA(‘Dynamic Range’!$1:$1)
counts the number of columns containing data in
Row 1. This gives us the width of the desired range. In cell G12 the formula =SUM(data) returns the sum (135)
of all the numbers in the range A1:D9.
To show that our named range is indeed dynamic add a name to cell
A11 followed by data in B11:D11. If you
like, also add some Product 4 data to column E. Your formula in cell G12 will
automatically update your SUM formula to include the newly added data.
Figure 2 Creation of
a Dynamic Range that Updates when new rows and/or columns are added to the
Creating a Chart that
Returns the Last 6 Months of Sales
The worksheet Last 6 contains monthly sales of a product. We would like to create a chart (see Figure 3) that always shows only the last 6 months of sales.
Figure 3 Showing the Last 6 Months of Sales
To create this chart we used formulas to create two dynamic ranges:
The range Months: OFFSET(‘Last
The range Sales : OFFSET(‘Last 6’!$B$3,COUNT(‘Last
The Months range contains 6 cells
in column A. The range starts five months
before the most recent month, because then the range contains six months (in this case (months 23-28
which are located in rows 26-31). Similarly the Sales range currently returns B26:B31.
To create our dynamic chart first select all the data (the range A3:B31) and then create a scatter chart with lines and markers (the 2nd choice.) Click on your data and in the Formula Bar you will see the formula
Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won over 40 teaching awards at Indiana University. He has written over a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Excel 2019 Data Analysis and Business Modeling", and "Mathletics." Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! Champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks.