[ExI] ai class at stanford

[Kelly Anderson]

On Wed, Aug 24, 2011 at 9:22 AM, BillK <pharos at gmail.com> wrote:
> On Wed, Aug 24, 2011 at 2:41 PM, Kelly Anderson wrote:
>> What we really need for spreadsheets to more fully reach their full
>> potential are:
>> 1) Hosted in the cloud (ala Google Spreadsheets)
>> 2) Ability to reference values from other spreadsheets with live
>> updates (Hyper-references)
>> 3) Ability to access data from anywhere inside the spreadsheet. (HTTP,
>> web services, etc.)
>> 4) Ability to send notices from Excel to other environments. (email,
>> sms, web services, etc.)
>>
>> Give me that and I'll be all over programming in Excel.
>
> Yes, very nice, except for the elephant in the room.
>
> Generally it is estimated that programming in spreadsheets has about
> the same error rate as normal programming. i.e. pretty high. But where
> a program breaks, a spreadsheet still offers you neat rows and columns
> of figures. So most spreadsheets don't get a thorough debugging.
>
> <http://www.strategy-at-risk.com/2009/03/03/the-risk-of-spreadsheet-errors/>
>
> Quote:
> Audits done shows that nearly 90% of the spreadsheets contained
> serious errors. Code inspection experiments also shows that even
> experienced users have a hard time finding errors, succeeding in only
> finding 54% on average.
>
> Powell et alii settled for six error types:
>
>   1. Hard-coding in a formula – one or more numbers appear in
> formulas         75%
>   2. Reference error – a formula contains one or more incorrect
> references to other cells 11%
>   3. Logic error – a formula is used incorrectly, leading to an
> incorrect result    8%
>   4. Copy/Paste error – a formula is wrong due to inaccurate use of copy/paste
>   5. Omission error – a formula is wrong because one or more of its
> input cells is blank
>   6. Data input error – an incorrect data input is used

I certainly agree that spreadsheets need something like a test
harness... I think there is enough of a framework in Excel and .NET
that you could implement such a thing but I haven't looked into it
seriously. I am perhaps one of the better qualified people to look at
such a solution... so I'll put in on my "really like to do" list...
:-)

It's hard to think what such tests would look like, or how they would
best be expressed. Ideally, you would want to check the results with
another little spreadsheet... The difficulty, is that unlike
parameterized programming, it's hard to say what a "module" is in a
spreadsheet. Maybe you could do tests in C# or VB and execute them
against the spreadsheet, but that would require spreadsheet users to
have extra skills to test their spreadsheets, which isn't an ideal
situation.

There are more programs out there written in Excel than any other
environment, bar none. It is sad that there aren't good testing tools
for those many many programs. It is worth thinking about, and I have
some, but not enough yet. I'm still waiting for the AH HAH moment that
says I've stumbled into the right solution. It's been rolling around
in the back of my head for a few years though.

-Kelly