In our January and February 2019 newsletters, we discussed the OFFSET function. Recall that the syntax of the OFFSET function is
OFFSET(cell reference, rows moved, columns moved, height, width).
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 Range
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
=OFFSET(‘Dynamic Range’!$A$1,0,0,COUNTA(‘Dynamic Range’!$A:$A),COUNTA(‘Dynamic Range’!$1:$1)).
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 worksheet
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 6’!$A$3,COUNT(‘Last 6’!$A:$A)-5,0,6,1)
- The range Sales : OFFSET(‘Last 6’!$B$3,COUNT(‘Last 6’!$A:$A)-5,0,6,1.
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
= SERIES(‘Last 6′!$B$3,’Last 6′!$A$4:$A$31,’Last 6’!$B$4:$B$31,1).
To create the dynamic chart simply replace the ranges in this formula by our dynamic range names. You will now see the formula
= SERIES(‘Last 6′!$B$3,’Last 6′!months,’Last 6’!sales,1). Add new data and you will see that the chart always shows only the last 6 months of sales!
