How would you classify the sorts of spreadsheets you see most often?
From a usage pov I can think of:
- transport s/s used for emailing info
- data stores
- one time use scratch pads
- enterprise data correctors/reformatters/ETL
- reporting/presentation
- numerical analysis
- aggregation/consolidation
Anyone got a link to research in this area?
The question I am seeking to answer is ‘if you couldn’t have spreadsheets what would you convert this type of spreadsheet to’.
Its a bit arbitary, I’m not claiming any particular classification is better or worse than another, or that any particular s/s would fit in one and only one category.
Do you think a classification based on life-time, or size/complexity (assuming you can measure it in a valid way), or number of users has much value?
I guess one way would be to look at alternative techs and say this should be a relational database, that should be in OLAP, that should be a web page, this should be in code, and those should stay in spreadsheets. And some could be split across multiple techs.
Any thoughts?
cheers
Simon
Smurf on Spreadsheets 
Sunday, 17th May, 2009 at 6:06 pm |
How would you define these categories? I can’t see your 1st catagory being anything other than a subclass of either your 3rd or 5th category.
So, does numerical analysis cover any spreadsheet that uses formulas that call functions other than just COUNT, COUNTA, AVERAGE, MAX, MIN, IF, IS*, *LOOKUP, INDEX, MATCH, NOW, TODAY and maybe OFFSET? If so, you need another category for (purely) textual list processing, which would seem to be one of the most frequently performed tasks in Excel given the volume of newsgroup postings on the subject. Or would that fall into your categories 2, 4 or 7?
Anyway, this is a purely academic question. For processes assigned to people in non-IT/IS departments, usually the ONLY automatable software packages available to those people are Excel (but not Access – in my business experience, maybe 1 in 10 business users has Access) or the Notepad/Paint/Word/VBS/IE stack. Nearly all those people will [mis]use Excel since it’s the only reasonable alternative. Unless they were given other, perhaps more appropriate software ALONG WITH the necessary training to use that software, they’re going to continue using Excel no matter what the more appropriate alternatives may be.
Spreadsheets/Excel are overused because spreadsheets/Excel is all too often the ONLY tool in a user’s toolbox. When the only tool you have is Excel, all problems or processes look like spreadsheets.
Sunday, 17th May, 2009 at 8:46 pm |
I think you have missed a category – at least for the work that I do – it might be considered numerical analysis, but that seems too theoretical.
Many of my clients use spreadsheets to manage a specific process in their business, and many of those use it for pricing/quoting. Pricing/quoting/estimating can be a complex analysis and one where mistakes are not tolerated well (under or over-pricing is particularly troublesome for the obvious reasons). Usually the spreadsheets have macros to automate some of the manual work, and to make it easier for an average spreadhsheet user to successfully complete. Is that what you had in mind for numerical analysis?
–Charlie
Monday, 18th May, 2009 at 12:58 pm |
Simon:
What disappoints me is that everyone spends so much time trying to find technologies to replace all thos “out of control” spreadsheets and in the end, if the truth be known, a spreadsheet is more often than not the best home for most apps that they are used for already.
I have found that by tapping into existing data sources thru SQL Server or Data Analysis Server or BO universes on SQL Server or Oracle or Essbase or whatever backend Excel make s a GREAT platform for so many of the categories listed by you. Alternatively there are times when Excel itself is the best store for data that doesn’t necessarily fit the Table model.
Bottom-line it’s about time the world realized the power that sits on every desktop right now rather than expending so much effort in seeking out alternatives that in the end are mostly going to be a disappointment in the long run (IMHO) :-)
Biggus
Tuesday, 19th May, 2009 at 11:09 am |
Tuck University carried a survey about spreadsheet usage (“A Comparison of Spreadsheet Use with Different Levels of Experience”) that you can find here
http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs.html
Friday, 22nd May, 2009 at 11:56 pm |
From my perspective a spreadsheet would be better described as a number processor, and most of what I do could be put under the classification of numerical analysis, but I think it is worth subdividing that for cases where the spreadsheet is used to process data for input to another program, or coming out of another program.
A typical structural analysis job for me consists of:
– set up the model by entering a few numbers into a purpose designed spreadsheet.
– transfer the model to the structural analysis program and run it. This may be a single step process, or it may involve transferring the data back to the spreadsheet for adjustment and sending it back again.
– extract the strain, stress and force results from the analysis program for post-processing, presentation in tabular and/or graphical form, and carrying out the design calculations (i.e. choosing a suitable size, number, position, and material for all the components).
– repeat until the model being analysed matches the end design.
It’s often said that a spreadsheet is the second best tool to do almost anything, but in the case of the pre and post processing steps in the above process I can’t think of a better tool, or one that even comes close. That’s not to say that Excel is perfect, far from it, but the basic multi-sheet spreadsheet model, with easy to use programming and graphing facilities built-in, is pretty well perfect for the job in my opinion.
Sunday, 24th May, 2009 at 12:14 am |
A major use I make of Excel is for creating interactive reports that enable my users to do querying with Autofilter so they can mine data. Typically I use various databases, Perl scripts, and other analysis tools to produce a tabular report which I then pull into Excel and set up for autofilter. Also I use Conditional Formatting to highlight cells of interest for various reasons (Conditional Formatting with formulas can do some pretty fancy scripts).
Generally I’ll spend an hour going over the report with the user, and more than half that time is spent showing them how to use Autofilter. I put some metadata columns at the left side so they can be used as filtering criteria.
Saturday, 23rd July, 2011 at 5:11 am |
You have try a lot on spreadsheet but the types are till not clear to me…… thanks…..