Spreadsheet complexity (Excel 2000 SR-1)

Topic

New Reply

My client has asked me to review a number of spreadsheets in one of the departments to determine how many complex spreadsheets they have. The problem is – what constitutes a complex spreadsheet? Does anyone know of any studies that have been done or criteria available in the public domain that might give me, at least a place to start, or some things to definitely consider. Any insight that anyone can provide is greatly appreciated

Ron M

Reply | Quote

Replies

(Edited by HansV to activate link – see Help 19)

Ron,

This following white paper doesn’t directly address your question, but it could be of some value with your project.
It is titled: “What We Know About Spreadsheet Errors” and was written by Raymond R. Panko at the University of Hawai

Reply | Quote

I would ask the clients what they consider COMPLEX.

I have found many people think:
Conditional formattting
Autofilter
single Charts that pullup multiple datasets to show “different” charts

Are all considered “complex” though they are pretty “standard” in many things I do. Macros could be considered complex, but the less the user sees of it the less they would consider it complex.

Steve

Reply | Quote

In which case you’ll get n!+1 answers , where n is the number of respondents …
Still, I agree it’s the best way to get an understanding.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I agree. I just think you need to get their opinions to get a “feel” for their comfort level with excel and try to understand WHY they want/need to know this and that can help define what they want.

Since THEY are asking to FIND COMPLEX sheets and count them, the TASK should be on them to DEFINE complex!

Steve

Reply | Quote

Well probably:

    N(Lounge)>N(Client)

Reply | Quote

Thanks folks, I am glad to see there is such agreement. Jim, I am familair with Ray Panko’s work and (unfortunately) he does not address the concept of a complex spreadsheet. So I guess I will do what I always do as a consultant, wing it. I suspect that there are degrees of complexity that include the number of worksheets in a work book, number of cells in a worksheet, use of pivot tables, formulas, conditional formatting, linkages, etc. As for having the people who requested the survey (management) prepare the criteria…

Thanks

Ron M

Reply | Quote

Complexity is relative to the skill of the user. Think of magicians! Just to continue the debate here’s my measure of complexity:

C=((tR/tU-1)>0)

Where C is complexity as Boolean, tR is the time to rebuild from scratch to accomplish the same result, and tU is the time a new User will take to understand the existing speadsheet.

Reply | Quote

I like your formula, but I think that in general the more COMPLEX the spreadsheet (and hence the larger tR is) the EASIER it should be for the user to use (lower tU). Many Users will think that COMPLEX spreadsheets are simple since they are simple to use.

Conversely I could create a SIMPLE workbook, Add a lot of SIMPLE bells and whistles (cond formatting, Autofilter, basic formulas) and many users will PERCEIVE that the workbook is complex (even if it is EASY to use) since they have NO idea how to do some of those things.

In many ways, the PERCEPTION of complexity is proportional to the SKILL-Level of the User. The ACTUAL complexity is more related to the SKILL of the Creator and the task requirements. I have seen spreadsheets that seem designed by Rube Goldberg (simple tasks made overly complex) and I have seen COMPLEX tasks put down into a single array formula.

Steve

Reply | Quote

Thanks John and Steve. A whole new light… What it looks like is that we really have two axes here, a vertical one with spreadsheet complexity going from simple to moderately complex to highly complex and a horizontal one for user skill going from beginner to intermediate to advanced. One could then identify areas of interest as far as managing risk with errors etc. Thanks for the input.

Ron M

Reply | Quote

I should note that my post was half joking. But the way I read Steve’s remarks, and he makes an excellent point, is that you should have three axes; one for efficiency (the ability to get the results with the least effort), one for density (the size and auditability of the work), and one for user skill.

Do you have enough to baffle your client into larger fees?

Reply | Quote

I think more like 4 axes (Notice how these thinigs get more complex) as we go!
1) User skill level
2)Efficiency (the ability to get the results with the least effort) aka “user-friendliness”
3)Complexity of the spreadsheet (even though this was the ORIGINAL question) I doubt we are any closer to an answer. Size, VB code, number of sheets, number and type of formulas, UDFs, Userforms, controls, etc all contribute to this.
4)Difficulting in making changes (John put this I think In “auditability”). This is INDEPENDENT of complexity. I think it is a function of “programmer expertise”. Did the programmer use dynamic range names so when changes are made it auto – updates. Did he/she use range names in the code or was the ranges hard-code? If you change the number of columns in a datarange, do you have to search through 2500 lines of code to change all the appropriate 5s to 6s or is it related to the columns in the datarange and is “updated automatically”. Is it documented?, etc

But again, As I mentioned earlier, not all are completely independent! The more complex (in general) the easier it is to use. if it is easy to use, the “user-skill” level should be “unimportant”. The novices can get anything they need with a “click of a button” and the power-user can have options available to customize to their heart’s content.

I still say, that the ORIGINAL question of complexity needs to be defined by whomever asked for a number of complex spreadsheets. Maybe they only need to know how many spreadsheets they have that contain macros? Maybe they need to know how many are > 4 Meg? Or how many have >75% of the total cells as formulas? Once you get the “metric” defined in some measurable way, you can work on a program to look at the workbooks and answer the question.

Steve

Reply | Quote

The points that you make are all very well put.

From a management point of view, however, the criteria could be boiled down to:

• If we had to change/update this spreadsheet, would we require a dedicated specialist (internal or external)?
• If we had to rebuild this spreadsheet from scratch, would it require more than X days of in-house resource?[/list]For these purposes, legacy knowledge of existing staff could be argued (in many cases) to be of a “dedicated specialist”.

Reply | Quote

Fair enough, but the competency of the builder/rebuider is relevant, which get’s back to Steve’s point about efficiency of the spreadsheet. A less skilled builder might build something unnecessarily complex (like most of my work stuff).

Reply | Quote

Competency is in no way in dispute, but, given the original question, it could take quite some 2×4 before the type of management mentioned could recognise it.

One definition of management is: “People who are paid a lot of money to make dumb decisions.” Am I speaking from experience?

Reply | Quote

>”People who are paid a lot of money to make dumb decisions.”

Or in my case “People who are paid a bit of money to make questionable decisions.”

Reply | Quote

I actually think we are getting somewhere in this discussion! Modifying my thoughts based on recent posts:

Management (I think) should probably be concerned about 3 items:

1) The ease of use of the workbook/application. How “easy” is it for the INTENDED users to use. Are they scientists, unit operators, computer programmers, etc. I think (and I grabbed these numbers from the same place our marketing folks get their budget forecasts):
5-15% will refuse to use it, fight you all the time, or are such “novices” nothing will make them happy. IGNORE THEM in your study

15-35% are more computer savvy and they will get most of what you do even if it is NOT simple. Use them for pointers on how to improve it later, but don’t look at their ease in “usability”

The other 50 – 80% are the ones to “please’ and to gear the program to. How “easy” is it for them to use!

2) How easily can it be maintained as it is: day-to-day, week-week, year-year. How is new data entered, does the data transition to the next month or next year of use without a lot of manual manipulation. Does one person enter in all the data, what happens if he/she is sick or retires, can someone else do it?

3) How easy can it be modified. If management wants to plot something new, is it a major chore to overhaul the database and datatables. Will it require complex macros and revamping all the code. Or was it built on sound programming principles, module design, some relational elements, named ranges (some dynamic). Was it built to be customized and changed or only built for the one purpose and any change will be almost starting over?

I think these 3 issues might be more important to a company/client than the “complexity question” originally posed. The complexity doesn’t really answer any of these and when I make worksheets, these are more important to me and to the users.

Steve

Reply | Quote

Hi Steve,

taking up the theme in your previous comments, a great deal of complexity can be built into a system to simplify the user’s work (or make it a more enjoyable experience).

Take for example a spreadsheet holding payee data it might be a simple thing to add/update a payee’s details. If the user’s task is to then produce a fortnightly report showing how much each payee received, this could be tackled in a number of ways, including:
. copying the data then deleting all unwanted rows/columns
. sorting and reporting only the required rows/columns
. using a pivot table to extract & summarise the data
. pressing a button on a toolbar to generate the required report

The first of these might be based on the simplest workbook but actually entails the greatest amount of user effort, whilst the last might be the simplest to use but requires the greatest level of effort to set up. Depending on one’s perspective, either of these could be more complex than the other.

Cheers

PS: The Law of Conservation of Compexity (paraphrased) holds: “Complexity can only be created or increased; it cannot be destroyed. You can only simplify one part of a system by increasing the complexity elsewhere in the same system”.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I think complexity also increases dramatically for each person (both programmer and user) involved in the “simplification”. The increase starts showing Exponential growth when management starts to get involved.

Steve

Reply | Quote

This has been a good thread for me, and I am further investigating all of these. Raymond Butler has a paper titled Is This Spreadsheet a Tax Evader?. On pages 3-4 he gives additional issues/questions related to complexity.

How many external references?
How many unique formulas? (not replicated in a worksheet)
How many original formulas? (copied within a worksheet)

What are the recalculation settings?
How many macros? (obviously a question is: how complex are the macros?)
Any hidden rows/columns?
What kind of protection to prevent inadvertent errors?
Is consolidation used?
Any named ranges? How many?
Any named formulas?

Any inappropriate techniques used? (i.e. business planning tools [Goal seek, Solver, etc.] that are inappropriate for financial accounting)

He also identifies some cells as “high risk” – those that appear arithmetically and logically correct but may contain errors.

Then on page 5 he gives other information that can be useful in determining its complexity and ease of use:

# of worksheets in each workbook?
# of formulas…
# of constants…
# of links to other files…

How many times is the spreadsheet used (copied) – which multiplies the possibilities of complexity (beyond the individual).

———-

Just a few further thoughts, nothing original – but definitely thought-provoking, for me at least.

Reply | Quote

This is a great discussion about workbook complexity. (Has THIS discussion become complex?)

Complexity is so subjective. I work daily with a workbook that takes up 1.5 Mb each. It has nearly 20 worksheets. My boss considers it too complex for him to work with. (That’s my job security! ) However, most of the Lounge contributors would consider it very simple. It’s merely “tons” of references from one sheet to another, and two macros – one to delete a worksheet, and one to run a three condition filter.

The reference to magic is apt. The client says “How’d you do that?”, and (more or less) gladly pays the consultant. The question Ron M needs to ask his client is “What part of Complex don’t you understand?” Are these worksheets complex for the user? Or are they complex to maintain?

Both these questions are discussed here. Once Ron gets this clarified with his client, he’ll know just what to do (but he’ll still be winging it, magically!)

Reply | Quote

Looks like I opened a can of worms here…well maybe not a COW, but at least a workbook with many worksheets, some different, some with similar thoughts in them. I am gradually coming to the thought that spreadsheets are not simple or complex in and of themselves, rather there are degrees of complexities and that with some SWAG thinking, one could, notice I said could, develop a complexity index based on things like general layout – rows, columns, no. of worksheets in the workbook etc. together with Functionality considerations. A colleague of mine also noted that I might want to consider the financial risk aspect of a spreadsheet – some of you made have heard about the recent $28 million cut and paste error made in a Utility company.

There is obviously lots of things and aspects to consider and I thank all who have contributed to this for my sake. It has been an interesting and rewarding discussion.

Ron M

Reply | Quote

Maybe you should have two for density – one for the size and auditability of the work and one for user

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

[indent]

---

that include the number of worksheets in a work book, number of cells in a worksheet, use of pivot tables, formulas, conditional formatting, linkages

---

[/indent]

Given this, I would set up a spreadsheet to give numerical value based on the numbers, and then weight each approrpiate category based on the skill formula above … LOL

Reply | Quote

John, thanks for your remarks. I am not quite sure how I would measure the efficiency side of things and as far as baffling my client into larger fees…his response would be I am sure.

Shades, your idea is similar to what I was thinking. What I have done (and I will see what feedback I get), is to prepare a white paper with my thoughts on the topic and given it to a couple of major spreadsheet users in the customer area to review and provide feedback. I will be interested to hear what they have to say.

To everyone that contributed to this discussion, a heartfelt thanks and back to .

Ron M

Reply | Quote

Aaron Blood has set up a simple criteria to determine spreadsheet level of skill. Have a look, you might find it helpful, at least as a starter.

http://www.xl-logic.com/user_scale.html%5B/url%5D

Reply | Quote