Contents
There are situations when it is not known in advance which cells on the sheet need to be counted. For example, let’s imagine that we need to implement a small transportation calculator in Excel to calculate the travel distance from one given station to another:
In the drop-down lists in the yellow cells F3 and F5, the user selects the stations of departure and destination, and in the green cell F7 the sum of all cells in the specified “window” on the sheet should be calculated. To travel from Ostankino to Khovrino, as in the figure, for example, you will need to sum all the cells in the range circled in green dotted lines.
How to calculate the sum is understandable, but how to determine the range of cells that need to be summed? After all, when choosing stations, it will constantly transform?
In such a situation, the function can help DISPOSAL (OFFSET), capable of issuing a link to a “floating window” – a range of a given size, located in a specific place on the sheet. The function syntax is as follows:
=DISPLACEMENT(Starting point; Shift_down; Shift_right; Height; Width)
This function at the output gives a reference to a range shifted relative to a certain starting cell (Starting point) a certain number of rows down and columns to the right. Moreover, the size of the range (“window”) can also be set by parameters Height и Width.
In our case, if we take cell A1 as a reference point, then:
- Reference point = A1
- Shift_down = 4
- Shift_right = 2
- Height = 4
- Width = 1
To calculate the required DISPOSAL arguments, let’s apply the function first MORE EXPOSED (MATCH), which we have already analyzed, to calculate the positions of stations of departure and destination:
And finally we use the function DISPOSALto get a link to the desired “window” on the sheet and sum all the cells from it:
That’s all, problem solved 🙂
PS
Unlike most other Excel functions, DISPOSAL is a volatile (volatile) or, as they say, “volatile” function. Ordinary functions are recalculated only if the cells with their arguments change. Volatile ones are recalculated each time the any cells. Of course, this negatively affects performance. In large heavy tables, the difference in the speed of the book can be very noticeable (at times). For some cases, it turns out to be faster to replace the slow DISPOSAL to non-volatile INDEX or other analogues.
- Finding the position of a given element in a list using the MATCH function
- 4 Ways to Create a Dropdown List in an Excel Sheet Cell
- 5 ways to use the INDEX function