Mike Schaeffer's Weblog
Wed, 24 Aug 2005
I had a dream...
I literally dreamed about this last night. It would be wonderful if Excel supported formulas like this:
=LET(value=MATCH(item,range,0), IF(ISERROR(value), 0, value))
If you're into Lisp-y languages, it'd look like this:
(let ((value (match item range 0)))
  (if (is-error? value) 0 value))
The function call =LET(name=binding, expression) would create a local range name named name, bound (equal) to the value returned by binding, to be used during the evaluation of expression. In the example above, during the evaluation of IF(ISERROR(value), 0, value)), value would be bound to the value returned by MATCH(item, range, 0).

It's worth pointing out that this is slightly different from how normal Excel range names work. Range names in Excel work through textual substitution. With textual substitution, the initial expression would be logically equivalent to this:

=IF(ISERROR(MATCH(item, range, 0)), 0, MATCH(item, range, 0)))
In other words, Excel would treat every instance of value as if MATCH(item, range, 0) was explictly spelled out. This means there are two calls to MATCH and two potential searches through the range. While it's possible that Excel optimizes the second search away, I'm not sure that anybody outside of Microsoft can know for sure how this is handled.

Microsoft's current reccomendation for handling the specific ISERROR scenario in the first expression is this VBA function:

Function IfError(formula As Variant, show As String)

    On Error GoTo ErrorHandler

    If IsError(formula) Then
        IfError = show
    Else
        IfError = formula
    End If

    Exit Function

ErrorHandler:
    Resume Next

End Function
This isn't bad, but it requires that spreadsheet authors and readers understand VBA. It also imposes significant performance costs: calling into VBA from a worksheet takes time.

reddit this! Digg Me!

[/tech/excel] permanent link

Wed, 17 Aug 2005
Bell Labs group 1127 has been disbanded...
Group 1127, the group at Bell Labs that originally developed Unix, has been disbanded in a reorganization. I'm not exactly sure why this matters since all the original staff are gone and the remnants of systems research are elsewhere, but it did make the front page of Slashdot.

reddit this! Digg Me!

[/tech/general] permanent link

Mon, 08 Aug 2005
Nine things from MacOS X
I saw a list of nine things KDE can learn from MacOS X over on Planet KDE. This should go beyond OS X, most modern software could stand to follow this advise. (I'm a big fan of Item 2, single toolbars, and Item 3, simple default views. Toolbars got way too complicated around the time Microsoft introduced Word 6.0, and simple default views make sense for the simple reason that users should have to explicitly ask for more complex or confusing functionality).

reddit this! Digg Me!

[/tech/general] permanent link

Arc Hub
Paul Graham solicited comments on his Arc programming language a few years ago. These comments are online, and are very interesting reading. Lots of good comments.

reddit this! Digg Me!

[/tech/lisp] permanent link