Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Spreadsheet complexity (Excel 2000 SR-1)

    My client has asked me to review a number of spreadsheets in one of the departments to determine how many complex spreadsheets they have. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> 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 <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    (Edited by HansV to activate link - see <!help=19>Help 19<!/help>)

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    In which case you'll get n!+1 answers <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> , where n is the number of respondents ...
    Still, I agree it's the best way to get an understanding.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    Well probably:

    <pre> N(Lounge)>N(Client)
    </pre>

    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    Thanks folks, I am glad to see there is such agreement. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23> 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... <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

    Thanks

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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. <IMG SRC=http://www.wopr.com/S/grin.gif title="I hope you don't think I'm serious!">
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    Thanks John and Steve. A whole new light... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> 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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

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

    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

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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 <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> 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 <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> and back to <img src=/S/compute.gif border=0 alt=compute width=40 height=20>.

    Ron M

  14. #14
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Spreadsheet complexity (Excel 2000 SR-1)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •