This my one hundredth page for this blog
Imagine you are building or refurbishing or even extending a house or other property. One of the ideas you have is that the columns or pillars that are part of the property should have tiles attached to them: purely cosmetic, not structural.
The question turns to design and you think:
- All one tile? Same colour, size and material?
- Two tiles? Different colours and size, same or different material?
- Three tiles? …
There are many possible combination of tiling here, clearly. However, what I want to talk about here is the scenario of choosing
- four tiles
- different colours
- same size
- same material
… you can change any of this as you wish!
These four tiles can be arranged in any pattern or style you want; and I am going to assume that four tiles extend across the face of the column exactly, including the grouting (infilling): no overlaps or shortfalls …
Your tiles might look like this … just playing with ideas here:
|Tiles Vertical 1||Tiles Vertical 2||Tiles Horizontal|
|Tiles Offset 1||Tiles Offset 2||Tiles Random|
What’s this got to do with Excel I can hear you screaming at the screen??? Well, how did I
a) get all of those colours EASILY to be the colours required?
b) How did I randomise the results?
Ah! Now you can see there must be a little bit more to this than meets the eye!
Enter the number 1, 2, 3 and 4 wherever you want them on the column. In the case of the Tiles Vertical 1, all I did was put tile 1 in the first column and only tile 1, tile 2 in column 2 and only tile 2 … and the same for columns 3 and 4.
For Tiles Vertical 2, I have moved things around a little as you can see, but in large blocks of seven tiles.
It should be clear what’s happening in Tiles Horizontal and then again for Tiles Offset 1 and 2.
I selected the range for the entire column and opened Conditional Formatting … Equal to … I made any cell with the value of 1 equal to light blue background with the same light blue text colour. Similarly for numbers 2, 3 and 4.
With Tiles Random all I did was to program every cell with this formula
I kept the same colour formatting as I just described above and then, randomisation does the rest. You should find it is truly random and there is one feature that you are probably not ready for: with true randomisation, there will be ranges of two, three and even more contiguous cells with the same colour if the columns and the number of tiles are large enough. If I asked you to create your own random design without using Excel’s RANDBETWEEN or RAND() functions, you would not create contiguous ranges of cells of the same colour I am almost sure!
If you want to use this random styling idea please remember one important thing: when the worksheet recalculates, you will get a new and probably completely different random pattern. Take a screen shot of the column you like or copy the one you like, paste special, paste as values … that will preserve your favourite pattern.
With the vertical and horizontal and even the offset patterns, your shopping list is straightforward … with two exceptions:
Most scenarios are like this, the vertical stripes one:
Where we need to buy and use equal numbers of the four tiles we have selected.
For the horizontal stripes, however, we have assumed that we need 34 rows of tiles and that leads to this outcome:
Where we need 32 of tiles 1 and 2 but only 28 of tiles 3 and 4: watch out for that with your own columns!
Finally, for the random pattern, for a new pattern compared to one we show above:
We need 35 of tile 1, 26 each of tiles 3 and 4 and 33 of tile 4.
As a matter of interest, I pressed the F9 key six times to generate six brand new patterns and the new shopping lists are as follows:
You can see that the results of the random simulation vary considerable from a minimum of 18 tiles to a maximum of 37 tiles in this range of six simulations.
The formula for counting the number of tiles needed is as follows, the same for all pattern options, by the way:
I am sure you can work out what I did here!
There you are: simple really but maybe a little hint in there that you have learned from!