Excel - assign value to cell in irregular grid for a planted wall
October 12, 2019 11:55 PM Subscribe
Hi everyone, I'm working on a timber crib wall with a builder. With crib walls you can have planted cells across the wall according to some simple rules*. I want as random a pattern as possible so wondered if it was possible to do this in Excel 2016. I hope this sort of job would come up more often and this would simplify the design process.
Is this sort of thing possible in Excel? What is the best way to go about it? Is this sort of problem easier if cells just have a character in them? I have a colour tab version and a character version tab. Excel's RAND fcn looks like a timesink for the uninitiated, maybe someone here could shine a light on it for me.
pdf and xlsm dropbox
Rules
1 - planted cells can only be a single cell
2 - cells must be separated by a minimum of two non-plant cells
3 - no plant cells in the bottom row
4 - maximum planted cells 40
My wall has four starter cells where I really need a planted cell. The wall rises up a slope from right to left, and falls from upper right to left as the wall meets the upper slope. If objects need to go beyond the defined edge in order to produce a calculation that is fine.
Numbers outside grid; X is cell columns, Y is cell rows
If anyone's interested the actual plant cells are geotextile bags 500mm wide x 150mm high x about a metre long back into the wall.
Is this sort of thing possible in Excel? What is the best way to go about it? Is this sort of problem easier if cells just have a character in them? I have a colour tab version and a character version tab. Excel's RAND fcn looks like a timesink for the uninitiated, maybe someone here could shine a light on it for me.
pdf and xlsm dropbox
Rules
1 - planted cells can only be a single cell
2 - cells must be separated by a minimum of two non-plant cells
3 - no plant cells in the bottom row
4 - maximum planted cells 40
My wall has four starter cells where I really need a planted cell. The wall rises up a slope from right to left, and falls from upper right to left as the wall meets the upper slope. If objects need to go beyond the defined edge in order to produce a calculation that is fine.
Numbers outside grid; X is cell columns, Y is cell rows
If anyone's interested the actual plant cells are geotextile bags 500mm wide x 150mm high x about a metre long back into the wall.
Need a clarification on rule 2. Can the walk from one plant cell to the next that's used to evaluate their separation go diagonally or only orthogonally? In other words, are the following arrangements legal?
and
posted by flabdablet at 5:28 AM on October 13, 2019
plain PLANT plain plain plain plain plain plain PLANT plain plain plain plain plain plain(diagonals-included separating cells = 1, orthogonal-only separating cells = 2)
and
plain PLANT plain plain plain plain plain plain plain plain plain plain plain PLANT plain plain plain plain plain plain(diagonals-included separating cells = 1, orthogonal-only separating cells = 3)
posted by flabdablet at 5:28 AM on October 13, 2019
The way I'm thinking of approaching this is to put a plant occurrence probability in each wall cell. Also on the sheet would be a plain numeric cell that lets you adjust the threshold probability above which a wall cell will have a plant in it, and another numeric cell that tells you how many plant cells you've ended up with.
Initial setup would involve entering a 1 into cells that must have a plant, a 0 into cells that must not, and a RAND()-based formula I'll invent once you clarify rule 2 pasted and/or drag-filled into cells that might or might not end up with a plant in them. Cells that are not part of the wall will just be left blank.
Conditional formatting will hide all the numeric values and just give you background colours that look like your sample sheets.
You'd keep tweaking the threshold cell until you ended up with the right number of plants arranged in a pleasing way, then do select-all, copy, and paste-special of values and formatting into a new sheet to preserve the result.
Does that sound workable?
posted by flabdablet at 5:47 AM on October 13, 2019
Initial setup would involve entering a 1 into cells that must have a plant, a 0 into cells that must not, and a RAND()-based formula I'll invent once you clarify rule 2 pasted and/or drag-filled into cells that might or might not end up with a plant in them. Cells that are not part of the wall will just be left blank.
Conditional formatting will hide all the numeric values and just give you background colours that look like your sample sheets.
You'd keep tweaking the threshold cell until you ended up with the right number of plants arranged in a pleasing way, then do select-all, copy, and paste-special of values and formatting into a new sheet to preserve the result.
Does that sound workable?
posted by flabdablet at 5:47 AM on October 13, 2019
Best answer: Went ahead with a proof-of-concept. Just keep hitting F9 to force recalculations and/or tweak the number in D1 until you get an arrangement you like. D35 has the total count of planted cells. I added a few hidden rows and columns of empty cells around the edges of the wall zone, just so the repeated formula doesn't need modifying for cells near the edge.
I did this with a combination of LibreOffice Calc and Excel Online, so the conditional formatting is a bit wocked up, but you'll get the idea. Ideally the text in the planted cells would be the same colour as the background, like it is in the unplanted ones, and the planted cells would be a nicer green. You should be able to achieve this with desktop Excel.
The repeated cell formula assumes that both the patterns I listed above are in fact legal. If that's wrong, just add cells to the OR expression until you achieve your desired exclusion zone.
The sheet necessarily requires circular references, so you might have to turn on iterative calculation in your copy of Excel; not sure whether that's a per-workbook or application-wide thing.
posted by flabdablet at 10:03 AM on October 13, 2019
I did this with a combination of LibreOffice Calc and Excel Online, so the conditional formatting is a bit wocked up, but you'll get the idea. Ideally the text in the planted cells would be the same colour as the background, like it is in the unplanted ones, and the planted cells would be a nicer green. You should be able to achieve this with desktop Excel.
The repeated cell formula assumes that both the patterns I listed above are in fact legal. If that's wrong, just add cells to the OR expression until you achieve your desired exclusion zone.
The sheet necessarily requires circular references, so you might have to turn on iterative calculation in your copy of Excel; not sure whether that's a per-workbook or application-wide thing.
posted by flabdablet at 10:03 AM on October 13, 2019
Response by poster: wow samj and flabdablet these look amazing (sorry to not get back sooner; I have landscapes coming out of my ears)
Yes, both walks are legal:
plain PLANT plain plain plain
plain plain plain PLANT plain
plain plain plain plain plain
and yes that sounds very workable flabdablet
samj - is that a python IDE? I really no little about this world yet. I like how the text matrix limits the calculating area.
I will play with these later in the week and get back. I can imagine using these codes for so many landscape and ecology things.
posted by unearthed at 2:36 AM on October 14, 2019
Yes, both walks are legal:
plain PLANT plain plain plain
plain plain plain PLANT plain
plain plain plain plain plain
and yes that sounds very workable flabdablet
samj - is that a python IDE? I really no little about this world yet. I like how the text matrix limits the calculating area.
I will play with these later in the week and get back. I can imagine using these codes for so many landscape and ecology things.
posted by unearthed at 2:36 AM on October 14, 2019
Best answer: My explanation about probability above turns out to have been misleading, given how the proof-of-concept spreadsheet actually works.
There's only one actual probability on the sheet, in cell D1, and what that actually is is the probability that any cell that could have a plant in it, given the restrictions imposed by nearby cells, won't have a plant in it.
The cell formulae themselves just generate a random number using RAND(), which is guaranteed to return a number greater than or equal to zero and strictly less than 1, and compare that to the D1 probability value. If a particular cell's random result is strictly less than the probability, that cell won't get a plant.
So if you set D1 equal to 1, the random result will always be less than that, optional planting will always be suppressed, and the only planted cells you will end up with are those you started with.
Setting D1 somewhere between 0 and 1 will get you a different pattern of plantings every time the spreadsheet is evaluated. If it's generally returning results with not enough plantings, making D1 smaller will make it more likely to plant more.
Setting D1 to zero means that the random result will never be less than that, and planting will never be suppressed: every cell that the placement rules allow to have a plant will get one.
Now, it may well be the case that given any particular set of starting must-plant cells, there are multiple legal ways to fill in the rest of the wall with plants. Setting D1 to zero will make the spreadsheet find one of these, but because there is then no randomness effectively in use, the particular solution it finds will depend on the exact order it chooses to evaluate the cells in. I would expect that to be stable across runs, but not necessarily across spreadsheet application versions.
In particular, it's not at all guaranteed to find the solution that maximizes the number of planted cells. If you want to use this spreadsheet to search manually for that solution, use a suppression probability near but not equal to zero.
If you'd prefer to be manipulating a probability to plant rather than a probability to suppress, altering the cell formulae to check for RAND()>=$D$1 rather than RAND()<$D$1 will achieve that.
posted by flabdablet at 12:41 AM on October 15, 2019 [1 favorite]
There's only one actual probability on the sheet, in cell D1, and what that actually is is the probability that any cell that could have a plant in it, given the restrictions imposed by nearby cells, won't have a plant in it.
The cell formulae themselves just generate a random number using RAND(), which is guaranteed to return a number greater than or equal to zero and strictly less than 1, and compare that to the D1 probability value. If a particular cell's random result is strictly less than the probability, that cell won't get a plant.
So if you set D1 equal to 1, the random result will always be less than that, optional planting will always be suppressed, and the only planted cells you will end up with are those you started with.
Setting D1 somewhere between 0 and 1 will get you a different pattern of plantings every time the spreadsheet is evaluated. If it's generally returning results with not enough plantings, making D1 smaller will make it more likely to plant more.
Setting D1 to zero means that the random result will never be less than that, and planting will never be suppressed: every cell that the placement rules allow to have a plant will get one.
Now, it may well be the case that given any particular set of starting must-plant cells, there are multiple legal ways to fill in the rest of the wall with plants. Setting D1 to zero will make the spreadsheet find one of these, but because there is then no randomness effectively in use, the particular solution it finds will depend on the exact order it chooses to evaluate the cells in. I would expect that to be stable across runs, but not necessarily across spreadsheet application versions.
In particular, it's not at all guaranteed to find the solution that maximizes the number of planted cells. If you want to use this spreadsheet to search manually for that solution, use a suppression probability near but not equal to zero.
If you'd prefer to be manipulating a probability to plant rather than a probability to suppress, altering the cell formulae to check for RAND()>=$D$1 rather than RAND()<$D$1 will achieve that.
posted by flabdablet at 12:41 AM on October 15, 2019 [1 favorite]
This thread is closed to new comments.
I tried to do it in Excel but my vbscript is too rusty, so here's a python version. Some random output here. I'm sure there are bugs.
I don't think you could do it with cell formulas without going insane!
posted by samj at 2:24 AM on October 13, 2019