Re: [office-formula] What should we do if you reference outside a supportedrange?

[robert_weir@us.ibm.com]

"David A. Wheeler" <dwheeler@dwheeler.com>
wrote on 06/09/2008 10:42:21 AM:

> If an application does not support some column (say "ZZZ"),
and a formula
> tries to reference it (e.g., [.ZZZ1]), what should happen?
> 
> The "obvious" answer "that is an Error".  Should
we specifically say that?
> Should we permit an alternative (e.g., presuming that they are empty
cells)?
> 
> Anything else?
> 
> I'd like to clearly document what happens in this case.
> 

I could only see this happening from either user input
error, or from importing a spreadsheet created in an app that supported
larger ranges than your spreadsheet.  In both cases signally an error
would be the safe thing to do, since it warns the user that something odd
happened.  If you treat it as empty, some calculations will treat
it has zero's, and that could introduce hard-to-detect errors.

Also, if you make it an error condition (with no alternatives)
then you allow formulas to test the size of a spreadsheet.  For example,
you can detect whether your spreadsheet supports more than 256 columns.
 So if you want to know whether your spreadsheet supports 1,000 rows
and 1,000 columns then you can use the formula =ISERROR(OFFSET(A1,1000,1000))

-Rob