I am using RANK whose 2nd parameter is a "value-set". In my case, I want this to be all of the cells in column ‘R’ that are in the body of the spreadsheet — i.e. all the cells in the column not in the header or footer. So, for a cell in row 20 as an example and the body has rows 3 through 90, I want the range of cells to be from 3 through 90, not 3 through 20. At this point, this appears to be R$3:R$90.
The difficulty is I add a row each day to the bottom of the body for the current day’s values. I want column ‘Z’ in this case to be the RANK of the value in column ‘R’ for the entire column of rows 3 through 90. And when I add a row tomorrow, I want that range to be 3 through 91 for all of the cells. Thus R$3:R$90 has the problem that the end doesn’t update not just for the new cell in row 91 but in all of the cells in rows 3 through 90 as well.
It appears as if I can specify R as the value-set and I get exactly what I am looking for. This is what it looks like (2nd value):
I have never used this before and I can’t find any documentation about its use anywhere. If the word "collection" in the description of "value-set" is clicked on the Apple documentation page referenced above, a list of methods to specify a value-set are listed but simply specifying a column or a row is not mentioned.
This question has the main purpose of asking if my conclusions are valid. Is this documented anywhere or have others discovered this before? And if not, then this is a way to tell others of this new discovery.
I got R by clicking the R column heading at the very top of the spreadsheet.
