Sometimes a spreadsheet is just a blur of numbers and if there is a computation error or inputs that are out of bounds you need to visually and in real time notify user that there is a problem.
Most of the time we pull up a spreadsheet to do some basic calculations or use it as a modern day piece of scratch paper to work out some quick numbers. But, sometimes we can get into some complex problems and you don't want to do the same work twice, so you clean up the spreadsheet so you can reuse the analysis and another tool is born.
Design tools with lots of variables will eventually run you into some limitations with an equation or boundary conditions. This is when you want the tool to warn you or another user when the inputs no longer match the model's assumptions.
STYLE and CURRENT cell commands
The picture above shows a perfect example of a dynamic display. There are a list of values in cells B2 through B6. I summed the values in cell B7, but I also wanted to warn the spreadsheet user when the current sum is larger than 100.
Here is how I did it. I used two cell commands "STYLE" and "CURRENT" to test and modify the background color of the cell. Here is the command used in cell B7
=SUM(B2:B6)+STYLE(IF(CURRENT()>100; "Red"; "Default"))
- The Sum command sums the cells B2 through B6
- The Style command changes the style of the cell to either the default cell style or the custom style called "Red". We'll talk about how to create custom styles in a moment.
- The Current command, takes the current value of the cell. So, within the style command we create a test using the IF command and test if the current cell value is greater than 100. If it is, we use cell style "Red" otherwise, we use the Default cell style.
Creating Custom Styles
I'm sure a couple of you were wondering about how do I create a custom cell style "Red".
Simple, I'll walk you through it. Click on any cell and then hit the F11 key on your keyboard or click the "format" on the spreadsheet toolbar and then click the "Styles and formatting" from the submenu. When you do, you'll get this window:
You may only have a Default style in this box and the Default style is automatically selected when you bring up the "Styles and formatting" command.
Right Click the Default style and you will get a sub menu to either modify the selected style or create a new one as shown in the figure below.
Now Click New...
When you click new a new cell style textbox will pop up and you'll have a lot of different options for creating this new style like background color, cell border options, and options in different font or font effects and other style changes:
Here is the cell style textbox.
Give a name for your custom cell style. I used Red, but you could use Warning or any other name that suits your Customized cell style.
Now click on the background tab and select a color for your cells that will use this custom style. I used Red, but your color choices are up to you.
When you're done click the OK button.
If you duplicate the code example, you can put different combinations of values in B2 through B6 and you'll have a regular (Default) cell background until the values sum up to 100 or a greater value.
You might also want to add a text string explaining why you do not want values to be beyond a certain value. I did this in cell D7
The formula for that cell is =IF(B7>100; "Value exceeds 100"; "")
Two years ago I created a structural analysis spreadsheet that worked out the dead loads for a platform to support a 30 foot diameter yurt and I used green, yellow, and red background styles to notify how close the loads were approaching (and exceeding) the burst strength of the concrete piers
Automated testing Display example
In my software QA test work, I've used MS excel to instrument a series of database tests on a new ETL tool developed by a firm in Massachusetts. My macro tool ran a series of sql command scripts that create a user database, run it through the ETL, which updated a larger data warehouse, and then ran some incremental updates to validate the functionality of the ETL tool. On one excel spreadsheet sheet I had a column of tests status cells that were colored yellow, green or red, which represented untested, test passed, or test failed, respectively. The cell macro also put the text "untested","pass", or "fail" in the cells.
If I were to rewrite the tool in OpenOffice, I would create three styles for the background colors and use the following formula.
=IF(E18=-1;"FAIL"&T(STYLE("Fail"));IF(E18=1;"PASS"&T(STYLE("Pass"));"UNTESTED"&T(STYLE("Untested"))))
It's not fun to write, but once you've got one cell working, cut and paste makes it easy to replicate into other test cells.
Next month we'll talk about changing styles, colors, and cell display options in macros.
