Hi everyone,

I would like to know how to make a cell range reference that counts cells / cell contents until it has found a certain number of filled cells.

Let's say I have a column in my spreadsheet that tracks matches won in a game. Its cells contain either "W" for win, "L" for loss, or nothing. I want to have my spreadsheet calculate the average from the last 100 cells that actually contain text, but because there are empty cells randomly interspersed in the data, knowing how far I need to go up from the known endpoint of the cell range to reach 100 cells containing text is not straightforward.

I have already solved this problem for a version of the spreadsheet where there are no empty cells (and thus I only need to operate on a fixed range of 100 cells), but for this version I need some help on how to make the output cell calculate on the fly what the cell range it operates on should be. Can this even be done?