Cell range based on number of filled cells?

[Quizer]

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?

[Villeroy]

Don't store any non-information.

[Quizer]

Don't store any non-information.

That's not helpful. I need to be able to handle empty spaces. Every row in the spreadsheet contains information, just not necessarily in that column.

[RusselB]

A cell range is just that...a range of cells. Those cells are organized in rows and columns.
You can't have a range change size based on the number of cells that are not blank, but you can determine how many cells in a range are (or are not) blank. Please note that a cell that has a formula may look blank, but to Calc it is not blank.

You say that you have to be able to handle empty spaces. Is that so that you can read the information easily or so that you have the ability to fill in information in those blank spaces?
If the first, as far as Calc is concerned, it is a waste of space.

Rereading the last part of your post, I'm thinking that the AVERAGEIFS function is what you want. This function is not in the help file, but if you haven't turned off the balloon help, you will get a balloon showing the order of the parameters once you have entered

Code: Select all

=averageifs(

into the cell where you want the average.
While this may not be ideal, it may be close enough for what you are attempting to do.

[Lupp]

Assuming the concept of "AVERAGE" is shifted to the ratio of "number of W against number of W_or_L" in this case, a solution (even an enhanced one also returning the column where the actual evaluation is starting) would be very simple based on the TEXTJOIN function implemented in a usable way in LibreOffice V5.4 or higher. See attachment. (It's useless to try it with AOO.)

@ MrProgrammer: I'm interested in your even simpler solution without the mentioned function.

[Quizer]

You can't have a range change size based on the number of cells that are not blank.

That is exactly what I am trying to do. So you are saying it can't be done?
I know where my spreadsheet starts and I know where the last row containing data is. I can count the number of empty cells and I can count the number of cells containing specific strings, such as the letters "W" and "L". I can do all this counting and do some addition and subtraction and then turn the result of that math into a cell range offset for my (already existing) averaging function. The only thing I am missing is a way to count cells in a direction until it gets exactly 100 cells that have contents, then output the number of cells it took to reach that result.

You say that you have to be able to handle empty spaces. Is that so that you can read the information easily or so that you have the ability to fill in information in those blank spaces?
If the first, as far as Calc is concerned, it is a waste of space.

The reason there are empty spaces is the the column with the Ws and Ls is not the sum total of my spreadsheet. A "match" can be made up of 1-3 "games", each of which is a row in the spreadsheet, but only the total match result causes a letter "W" or "L" to exist in the column in question.

My previous solution was to get a rough average of how many games there are per match, and then use that to get a cell range that equates to "roughly the last 100 matches", but I would like to get "exactly the last 100 matches". If that's not possible, I guess I may have to go back to the imprecise solution. Thanks for trying!

[RusselB]

If you don't mind having to do a bit of extra work to track the matches, as well as the games, then I have some ideas that may work.
I'll do up a couple of example spreadsheets after work today and post them for you to consider, though getting a sample of your current spreadsheet may (probably will) make things easier as I can incorporate your current structure.