## Cell range based on number of filled cells?

### Cell range based on number of filled cells?

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?
Apache OpenOffice 4.1.6
Windows 10
Quizer

Posts: 3
Joined: Sun Mar 10, 2019 11:39 pm

### Re: Cell range based on number of filled cells?

Don't store any non-information.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Cell range based on number of filled cells?

Villeroy wrote: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.
Apache OpenOffice 4.1.6
Windows 10
Quizer

Posts: 3
Joined: Sun Mar 10, 2019 11:39 pm

### Re: Cell range based on number of filled cells?

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   Expand viewCollapse view
`=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.
OpenOffice 4.1.7 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5521
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Cell range based on number of filled cells?

Quizer wrote: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 …
Averaging is an operation performed on numbers. Explain in detail how you are going to average Ws and Ls. Attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

Quizer wrote: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
This will be simple using an auxillary column. But to provide more detailed assistance I need my first request answered.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3846
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Cell range based on number of filled cells?

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.
Attachments
a0097336LossLoss_1.ods
only working in LibreOffice V 5.4 or higher
Last edited by Lupp on Mon Mar 11, 2019 2:19 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Cell range based on number of filled cells?

RusselB wrote: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.

RusselB wrote: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!
Last edited by Quizer on Thu Mar 14, 2019 3:52 am, edited 1 time in total.
Apache OpenOffice 4.1.6
Windows 10
Quizer

Posts: 3
Joined: Sun Mar 10, 2019 11:39 pm

### Re: Cell range based on number of filled cells?

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.
OpenOffice 4.1.7 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5521
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON