In Microsoft Excel, changing the evaluated range on the spot requires a bit of work, but it is certainly possible thanks to the Offset () function.
Image: muchomor, Getty Images / iStockphoto
TechRepublic member Jeff has an interesting and challenging Microsoft Excel problem: he wants to apply a conditional count to the last seven rows of a data set that is updated daily – that means the solution must be dynamic. Right now he can apply the conditional count, but he has to update the formula every day, which is annoying. I’ll show you the solution I’ve suggested for Jeff: Use an OFFSET () function to accommodate the changing data range.
I use Office 365 desktop on a Windows 10 64-bit system, but this solution works in older versions and in the browser. You can work with your own data or download the demonstration .xlsx file (the .xls format does not support this solution).
LEARN MORE: Office 365 Consumer prices and functions
Why do you need Offset ()?
We have kept the data set simple: two columns and one row for each day (Figure A). The status values are the conditional values yes and no. Moreover, it is a table object.
Figure A
We deliberately keep the data set simple.
Jeff uses a counting function to return the number of Yes values in the last seven rows. With the help of cell references, he must update the reference every day to be able to process the new daily record. Jeff needs a dynamic formula that automatically adjusts the data range when a new record is added. We will use the Offset () function for this.
What does Offset () do?
The Offset () function of Excel returns a range specified with a specific number of rows and columns, depending on an anchor cell. This function uses the following syntax:
OFFSET (reference, rows, cols, (height), (width))
In short, you build a series:
-
reference is the first cell in the data set. In our case, that is C3 – the first status cell.
-
rows is the number of rows up or down that you want to reference based on reference.
-
cols is the number of columns to the left or right that you want to reference, based on reference.
-
height determines the number of rows that the returned reference must be. This is an optional argument and must be a positive number.
-
width determines the number of columns that the returned reference must be. This is an optional argument and must be a positive number.
Now let’s create an Offset () function that Jeff can use, starting with an input cell so that the returned range is dynamic.
How to use the input cell
Jeff wants the returned range to evaluate the last seven rows in the dataset. We can enter 7 in the expression, but let’s use an input cell instead so that Jeff can immediately change the size of the returned range; Jeff doesn’t need this flexibility, but maybe he does. Figure B shows the new input cell. E3 is a named range, n_rows. (Select E3 and enter n_rows in the Name box).
Figure B
The value in E3 determines how many rows are in the returned range.
With the input cell in place, you are ready for the expression that counts.
How to write the expression
The expression that we are going to use combines CountIf () and Offset (). The Offset () function returns a range that is evaluated by CountIf (). Enter the following expression in G3 (Figure C).
= NUMBER OFS (offset (C3, COUNTA (Table 1 (status)), 0, -n_rows-1), “Yes”)
Figure C
This expression counts Yes values in the returned range.
Perhaps the easiest way to explain how this works is to evaluate it in pieces:
-
reference is C3 the anchor cell – the top left cell.
-
rows is the CountA () function, which returns the number of cells in the Status (15) column. This value will increase with every new record. The table reference (status) can be a traditional cell reference, but we need the dynamic properties of the table object to make this technique work.
-
cols is 0 because the counting takes place in the Status column; we do not have to extend the returned range with columns.
-
height is -n_rows-1, the input cell minus 1, which returns 6.
-
width is not necessary because we do not extend the columns.
Let’s put it all together to see how it works.
= NUMBER OFS (offset (C3, COUNTA (Table 1 (status)), 0, -n_rows-1), “Yes”)
= NUMBER OF (OFFSET (C3.15.0.6), “Yes”)
= NUMBER OF (C11: C17, “Yes”)
= 3
The total number of cells is 15 and the returned range starts from there nine rows at C11: 15- (7-1) = 9. The returned range is C11: C17. This is why the dataset must be a table object: it automatically adjusts the last cell reference when you add new rows. If you were using a normal data range, this solution would not work.
Here is another input cell to try
Jeff doesn’t need more flexibility, but maybe so, let’s add another input cell: the conditional values Yes and No. We use a validation check for this purpose.
-
Select F3. Click the Data tab and then click Data Validation in the Data Tools group. If necessary, choose Data Validation from the drop-down list.
-
In the dialog box that appears, choose Allow and enter Yes, No in the Source list (Figure D).
-
Click OK.
Figure D
Add a validation check that provides the conditional values Yes and No. By limiting the choices, you prevent errors.
Then change the expression to refer to the new input cell:
= NUMBER OFS (OFFSET (C3, COUNTA (Table 1 (status)), 0, -n_rows-1), F3)
Replace Yes with F3 – that is the only change. You can now decide whether the function counts the values Yes or No and determine how many rows are counted (Figure E).
Figure E
Determine the range and count on the spot.
You will find that I did not use a named range for F3, as I did for E3. It works both ways – you can use the actual reference or use a named range for both input cells. There are good reasons to use named ranges, but in this case it doesn’t matter. You may encounter a situation where a named range offers more flexibility than a cell reference.
If you have a different solution for Jeff, share your ideas in the comments below.
Send me your question about Office
I answer questions from readers when I can, but there is no guarantee. Do not send files unless requested; initial requests for help that come with attached files are deleted unread. You can send screen shots of your data to clarify your question. Be as specific as possible when contacting me. For example: “Solve problems with my workbook and solve the problem” will probably not get an answer, but “Can you tell me why this formula does not produce the expected results?” power. State the app and version that you are using. I do not receive any reimbursement from TechRepublic for my time or expertise in helping readers, nor do I request reimbursement from readers who I help. You can contact me via [email protected].
Weekly newsletter from Microsoft
Be Microsoft’s insider for your business with the help of these Windows and Office tutorials and the analyzes of our experts on Microsoft business products.
Delivered on Monday and Wednesday
Register today
