PraxIS Jan. 2009

09-01 Contents: EuroCACS, SSQC, Euro news,Spreadsheet self-checking

ISSN 1649-2374 This issue online at   [Previous] [Index]

Systems Modelling Ltd.: Managing reality in Information Systems - strategies for success  


1) IT Risk
      EuroCACS 15-18 March Frankfurt
      Software & Systems Quality Conference 3-4 March Dublin
2) Euro
      Slovakia Enters Euro Zone
      European art and culture archives online
      Unfair commercial practices: advice to consumers
3) Spreadsheets
      Tips for cleaning up sloppy spreadsheets

4) Off Topic
14 links in this newsletter
About this newsletter and Archives
Subscribe and Unsubscribe information


Welcome to PraxIS

Happy New Year!  2008 certainly ended with bangs and whimpers; let's hope we make it through 2009 OK!

Patrick O'Beirne

_______________________________________________________ _______________________________________________________

1)  IT Risk & Quality

EuroCACS 15-18 March Frankfurt

EuroCACS, presented by ISACA, provides the most comprehensive training for IT audit, security and governance professionals in Europe.

WS2—Spreadsheet Auditing (one day) 14 March 2009

Ray Butler and I present a one day pre-conference workshop on Spreadsheet Auditing

Spreadsheet models are widely used to make vital business decisions and perform processes, but are known to be error-prone and high-risk applications. Despite the risks, they are often not tested. Testing can be tricky and time consuming for the auditor or reviewer, and if contracted out to specialist service companies in the field, expensive.

This one-day workshop:

The participant will learn more about:


Software & Systems Quality Conference

Venue: Croke Park Conference Centre, Dublin
Date: March 3rd & 4th 2009   or phone   (+353) (0) 1 657 2506

The keynote presenter is Sir Richard Noble, the man who broke the land speed record, achieving 633mph in Thrust 2 - on a budget a fraction of that of his competitors.


2) Europe news

Slovakia Enters Euro Zone 

Slovakia is a high-income economy with one of the fastest growth rates in the EU and OECD. It joined the European Union in 2004 and joined the Eurozone on the 1st of January, 2009 at the rate of 30.1260 korunas to the euro.


European art and culture archives online is a collaboration between universities, research institutes and content providers. A research prototype of a semantic search engine contains data of the Rijksmuseum Amsterdam, the Musée du Louvre in Paris, and the Rijksbureau voor Kunsthistorische Documentatie (Netherlands Institute for Art History). in The Hague.


Unfair commercial practices: advice to consumers  Consumer information on unfair commercial practices

National consumer associations in your country are usually aware of the rogue traders active in your area/country. They can advise you on domestic problems.

2. The European Consumer Centres Network (ECC-Net)

This network of consumer advice centres helps consumers specifically with cross-border disputes. It was set up in January 2005 by the European Commission in cooperation with the national authorities. The centres provide information and give advice and assistance to consumers with their complaints and the resolution of disputes.



3) Spreadsheets

Cleaning up sloppy spreadsheets

CFO Magazine had a popular series of articles in 2008 on reader's peeves and solutions to spreadsheet sloppiness: Spreadsheet "Worst Practices" Sloppy Spreadsheets: Readers Speak Out Sloppier Spreadsheets: How Bad Can They Get?

Many of the complaints about spreadsheets missing cross-foot checks or not being easily readable. So this month I'll list some tips on building self-checking into a spreadsheet in order to provide more confidence to the reader that your spreadsheet is robust.

1) Cross foot

The cross-foot check is to sum every column and every row and compare the sum of the grand total row with the sum of the row totals in the final column. Calculate the difference; if it is non zero the amount may help you identify where the error is. Have a cell that displays a large red error indicator if the difference is significant, so that even on printouts it cannot be missed. Excel always has small rounding errors from floating point arithmetic, which although as small as 1E-13 will still be different from zero. So test for the absolute value of the difference being greater than 0.01 or whatever number suits the scale of values you are working with. A good sign of a careful spreadsheet maker is that the front sheet (or whatever sheet is always printed) carries forward the error check indicator from every supporting sheet, so that a report cannot be embarrassingly printed without knowing that some supporting sheet has an error.

We also want to avoid the risk of forgetting to include a row in the subtotal or total rows. One way to check the grand total is to sum the entire table range including intermediate totals, grand totals, and right-hand totals, and divide that by four.

2) Balance

In Accounting 101 you learn that a balance sheet must balance. (Usually by defining owner's equity as a balance line equal to total assets less total liabilities.)  Chemical engineers prove their calculations by a mass balance that shows how all the material inputs end up as outputs. To prove that your spreadsheets have some data integrity, you need to look for any opportunity to verify that a total of inputs is the same as the total of outputs.

For example, if you have a total budget to allocate, the sum of the allocated amounts must equal the original total.

3) Proportion

When forecasting projects, there is often no known input amount to compare with an output. In such cases we look at the absolute value of changes or the proportional value of changes to see if they correspond. One might compare last year's increase with this year's to see if they are as expected. Unfortunately, expectation is a dodgy measure that works in stable conditions but not in the unstable real world - see 'Expectations' below.

4) Multiple plus ungood

We've all seen this kind of total calculation:


and know there is a high likelihood of a pointing error - that one of those references is pointing to the wrong cell; or that there is some reference overlooked in creating that chain. It arises when there are detail lines, intermediate totals calculated by =SUM() and to get a total of these intermediate figures, each needs to be individually selected. Here are some ways to get around this:

4a) Assuming that every detail figure is also included in the =SUM() intermediate formulas, simply calculate =SUM(B2:B67)/2.

4b) Replace all the =SUM( formulas with =SUBTOTAL(9,... Then replace the multiple-plus formula with =SUBTOTAL(9,B2:B67). The SUBTOTAL function ignores the results of other SUBTOTAL functions  in its range.

If the data is an export from an accounting system or database, or is a simple table of numbers, you can get Excel to automatically put in the subtotal formulas, so you don't have to. Ensure that every column has an identifying header; and that every row has an entry in a column that indicates what group this row belongs to, and the table is sorted by that column. Then choose the Data Subtotals command, specify the grouping column, and check all the data columns that you want subtotalled; and Excel puts them in for you. When you insert or delete rows in the table, simply re-do the Subtotals command.

4c) There may be other intermediate calculations that are only required for display and not for further calculation. They could be SUM or cumulative calculations that give a running accumulation from left to right. A way to exclude their result is to use the text functions =FIXED() and =DOLLAR() which produce text results that are excluded from SUM totals. Be aware that Excel will still treat them as numbers if they are referred to individually in a formula.

5) Room for expansion

How do you make sure the totals still refer to the correct cells if rows are inserted or deleted? Take for example a formula in B67 =SUBTOTAL(9,B51:B66)

If you insert a row at row 51 the formula now reads =SUBTOTAL(9,B52:B67)

If you insert a row at row 67 the formula still reads =SUBTOTAL(9,B51:B66)

Either way, the row you inserted is excluded from the calculation. On the Tools > Options > Edit tab there is a setting "Extend data table formats and functions" that may automagically adjust the formulas for you, but I would not rely on it.

To avoid the risk of missing an inserted row or column, always make sure every range to be summed begins and ends at a blank cell. If the first row consists only of a heading in the first column, that is readable enough. If not, you can enter in the first column the prompt "(Insert further rows below this line)" and in the last row "(Insert further rows above this line)". You could also fill the cells with ten underline characters, or make the interior colour black or blue and make the row a smaller height.

Another way to protect against insertion at the bottom of the column is: in B67 enter =SUBTOTAL(9,B51:OFFSET(B67,-1,0))

That looks like a circular reference to B67 in B67 but in fact the OFFSET function is interpreted as a reference first so in B67, OFFSET(B67,-1,0) is interpreted as B66. points out an alternative using INDEX to always refer to the row above:


However, both of those are more difficult for general Excel users to understand, and neither protects against insertion at the top of the range.

6) Expectations

An obvious flaw in relying on expectations is getting the answer you expect instead of the correct answer. Another is getting an answer that is materially different but not obviously so. It is unavoidable that we tend to look at the new in terms of the familiar, and when that raises questions it's useful. But when it does not raise questions that does not mean the answer is correct - merely that that particular test gave us no information.

Remember the Dilbert cartoon where he says to a person offering a spreadsheet checking "I don't think accuracy matters if no one can tell what it's for", and that person repors back to the puzzled boss "And Dilbert found no inaccuracies!"  Some people are better than others at finding errors, just as good developers can be many times more productive than bad ones.


Spreadsheet Check and Control: 47 best practices to detect and prevent errors  Our offer - free shipping to EU .



Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM

Thank you! Patrick O'Beirne, Editor

_______________________________________________________ _______________________________________________________

4) Off Topic

This photo is a souvenir of the Christmas season TV station ident for RTE 1. I'm on the left :-)

Screenshot of RTE1 Christmas 2008 ident


Copyright (c) Systems Modelling Limited, . Reproduction allowed provided this copyright notice is included.

We appreciate any feedback or suggestions for improvement. If you have received this newsletter from anybody else, we urge you to sign up for your personal copy by sending a blank email to   EuroIS-subscribe (at) yahoogroups (dot) com

For those who would like to do more than receive the monthly newsletter, the EuroIS list makes it easy for you to discuss issues raised, to share experiences with the rest of the group, and to contribute files to a common user community pool independent of the web site. I moderate posts to the EuroIS list, to screen out inappropriate material.

Patrick O'Beirne, Editor
"Praxis" means model or example, from the Greek verb "to do". The name is chosen to reflect our focus on practical solutions to IS problems, avoiding hype. If you like acronyms, think of it as "Patrick's reports and analysis across Information Systems".
Please tell a friend about this newsletter.
We especially appreciate a link to from your web site!
To read previous issues of this newsletter please visit our web site at

This newsletter is prepared in good faith and the information has been taken from observation and other sources believed to be reliable. Systems Modelling Ltd. (SML) does not represent expressly or by implication the accuracy, truthfulness or reliability of any information provided. It is a condition of use that users accept that SML has no liability for any errors, inaccuracies or omissions. The information is not intended to constitute legal or professional advice. You should consult a professional at Systems Modelling Ltd. directly for advice that is specifically tailored to your particular circumstances.
We guarantee not to sell, trade or give your e-mail address to anyone.
To subscribe to this Newsletter send an email to
EuroIS-subscribe (at) yahoogroups (dot) com
To unsubscribe from this Newsletter send an email to
EuroIS-unsubscribe (at) yahoogroups (dot) com
EuroIS is the distribution list server of the PraxIS newsletter. It also offers a moderated discussion list for readers and a free shared storage area for user-contributed files. The archives of this group are on YahooGroups website