Commenting spreadsheet formulas

One complaint often aimed at spreadsheets is that formulas in cells can’t have helpful comments embedded. Unlike VBA code for example. I’ll save my thoughts around code commenting for another post, and focus on spreadsheet formulas here. Ideally formulas should be so short and simple and well described by text in cells nearby that further description is not required. However that may not always be possible, especially when modifying some monster spreadsheet someone else developed.

One possible approach is to use the CHOOSE worksheet function, this takes an index number as the first argument, and then a list of choices for up to 29 (or so) further arguments. CHOOSE will return the item in the list found at the index given as the first argument.

This could be used like this:

=CHOOSE(1 2 or 3 ; My horrible formula; “short comment”; “long comment”) (I did this example in Open Office Calc hence the ‘;’ rather than Excels ‘,’ argument separator)

 CHOOSE function screen shot

Then depending on whether the first value (a named range called ‘view’ in this case) is 1, 2 or 3 you get either the formula (calculated as normal), short comment, or long comment .

I’ve uploaded an example to codematic here, I probably could have thought up a better example, but this one illustrates the point. There is an Open Office (v2.0) format file (.ods) and an Excel one (.xls). I tested the xls in Gnumeric too.

There is also a separate XLM macro file to provide the FORMULA worksheet function that OO has that Excel doesn’t, called FORMULAwsfxl.xls). You don’t need that to see the CHOOSE example, but I’ve found it to be a very handy formula. It takes one argument, a cell reference and returns the formula in that cell as a string. If you want to use it just copy the macro sheet into the chooseeg.xls workbook and all the #NAME errors at the bottom should go away. I left this out of chooseeg.xls, as some people may not want to open files with XLM macros in. I could have done the function in VBA but XLM is much faster. Its on my list of things to convert to an xll.

I have no idea of the performance impact of putting loads of CHOOSE formulas in a big workbook, but I wouldn’t be surprised if it was very minor. If people think its a decent approach I’ll do some proper testing. I didn’t try it wth array formulas either.

Please have a look, and leave a comment with what you think, is it a good approach? Are comments better? Would you/do you use it? Any better ideas? Did it work right? I think it would work better on a non tabular example, are there any other limitations you see?

cheers

Simon

Advertisements

14 Responses to “Commenting spreadsheet formulas”

  1. sam Says:

    Hi Simon,

    I dont like comments in formulas, nor do I like comments in cells. I prefer to use meaningful short names to make formulas more readable (as in For 2) despite some limitations that names have for audititng
    Imagine putting comments in For 1 …. I think it would just increase the confusion

    For 1
    =SUMPRODUCT((SALE_RECORD!$D$2:$D$143=B122)*(SALE_RECORD!$E$2:$E$143=C122)*(SALE_RECORD!$G$2:$G$143=D122)*(SALE_RECORD!$K$2:$K$143))

    For 2
    =SUMPRODUCT((PROD=”MAC”)*(CUST_TYPE=”INDIVIDUAL”)*(MONTH=”MAR”),(TOT_SALE))

    Regards
    Sam

  2. Ken Puls Says:

    Maybe I have little faith in users, but…

    1) If a non-power-user can’t evaluate a regular formula, I think the Choose formula would confuse them more.
    2) For the power user, I don’t know how much help it would be anyway. You can toggle to show the descriptor, but chances are it will be cut off by the next cell anyway.
    3) I’m not convinced on the load in a large workbook…

    Personally, if I was going to document a formula, I’d do it via one of the following two methods:
    1) Use a comment. (At least it’s obvious where it is, and you can print them off as worksheet footnotes)
    2) Use an input message in a data validation rule. This way it just pops up when someone mouses over it.

    I prefer number one.

    Cheers,

    Ken

  3. Marcus Says:

    Hi Simon,

    I could have sworn I had read of a technique to add a comment to Excel formulas in a similar fashion to Lotus 1-2-3. From what I (vaguely) recall you could do by adding a semicolon at the end of the formula like this: +A1*B5; Calc gross income

    Unfortunately I couldn’t relocate the article. Also I found that some people started adding essays to the ends of their formulas.
    Anyway, I tend to use cell comments to annotate formulas in Excel or they are detailed in a separate document.

    I tried the sample spreadsheet, Simon. The main concerns I had were:
    > I (or a user) would have to know to add CHOOSE to any formula they
    wanted to document. I just felt this was too fiddly.
    > Having the description showing (long or short) was of little benefit as the
    full description was truncated by any cell to the right which had contents.

    I’m reasonably happy with Excel’s functionality to print cells comments. Some VBA would make short work of displaying formula comments in a worksheet, if that were required.

    Cheers – Marcus

  4. Damon Longworth Says:

    Marcus

    You can use the N() function. Something similar to:

    =SUM(A1:A7)+N(“This formula is too complicated”)

    Damon
    http://www.ExcelUserConference.com

  5. Simon Says:

    Thanks for your inputs everyone.
    I’ve never used this myself, it was inspired by some discussion on the Eusprig list ages ago. I’m totally undecided if its useful or not, although based on your comments I’m swinging towards putting it in the ‘pointless’ basket.
    The thing I like is being able to turn it on or off universally, although thats little use if the cell to the right is populated.
    One of my favorite review tools is a list of unique formulas which is why comments within the formulas might be useful. Damons N() suggestion is probably the best compromise for those occasions I need to put in a disclaimer (eg, when editing other peoples stuff):
    =some horrible monster +N(“Sorry I didn’t dare make this change a simple way”)
    What about the FORMULA function? I can see plenty of uses for that, I’ve seen plenty of VBA ones, but its nice to see OO has it natively. I havent properly tested my xll one, but I intend to expand it to include some of the standard test driven stuff – one day!
    Thanks again
    cheers
    Simon

  6. Simon Says:

    Hi Damon btw

  7. Ross Says:

    Hi.
    I did once read something about putting comment into formulas. At the time I passed it by, but over the years there have been times I wished I’d paid it more attention.

    Simon, I think the choose method is to much effort, I really like the +n() one and will make use of it right away. You can also use this after any expression – more like code, ie

    =C6+N(“Had to get the number of trips per week until May 25, at rate a”)+D6+N(“then add rest at rate b :-p”)

    I hear what Ken says, but I often don’t kneed to know why or what the formula is doing, but why I made it do that in the first place – i.e. the business logic.

    For some reason I have never liked cell comments. I don’t like the way they work, I think they are clunky. Also so what’s with red for a cell comment and green for an error?!?!?! And lets not get started on smart tags! Anyway I don’t think cell comments were designed to documents formulas? – although that might be wrong.

    I guess I wish it was possible to build formulas like code and comment like it too. But at this point we are rather moving away from the whole paradigm of a spread sheet.

    Good topic Simon, Keep up the ferocious pace!

    Ross

  8. Simon Says:

    Ross
    Funny on cell comments I’ve always avoided them too, I never noticed the irony of the colours though!
    cheers
    simon

  9. Ken Puls Says:

    The red/green thing is funny. (I never noticed the irony of that either.) I’m betting that comments came first, though and were red. Error checking came later, didn’t it? I’m guessing that no one wanted to confuse the users by changing them.

    You can turn off the indicators all together in Excel though. (Pre 2007 it is) Tools|Options|View –> Set comments to “None”. Presto, no more irritation for you, Ross. :)

    Also, for auditing, you can print off the page and have the comments show up at the end (or in line, I believe, although I’ve never done that). PageSetup|Sheet –> change the comment drop down.

    Personally, I find them way less intrusive that making notes IN the formula. Again, I also wonder if there is a perfomance hit by adding an N() to the end or each formula. It must, after all, be evaluated as well.

  10. Ross Says:

    well you might not add the n() to the end of each formula!

  11. Bob Phillips Says:

    Ken, there is bound to be a perfomance hit, it is another function to evaluate, and worst of all it involves text.

    Taking it further, N fails if dealing with text,
    =A1&” should be paid on: “&TEXT(B1,”mm/dd/yyyy”)+TEXT(N(“due date of payment”),””)
    will clearly fail, but can be made text like so

    =A1&” should be paid on: “&TEXT(B1,”mm/dd/yyyy”)&TEXT(N(“due date of payment”),””)

  12. Bob Phillips Says:

    Oops that first formula should have been

    =A1&” should be paid on: “&TEXT(B1,”mm/dd/yyyy”)+N(”due date of payment”)

  13. Simon Says:

    I’ll maybe do some testing on performance, for interest, I think we’ve pretty much decided documenting with CHOOSE is dead in the water.

  14. Ken Puls Says:

    “there is bound to be a perfomance hit, it is another function to evaluate”

    My thoughts exactly, but I never tested it. Seems intuitive, though, I agree. ;)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: