OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Re: [office-formula] 3-d references

Hi Andreas,

On Wednesday, 2008-11-26 12:53:40 -0700, Andreas J. Guelzow wrote:

> I do not have any easy access to any version of MS Excel so I can't try
> it out.

I just did, see below.

> [...]
> > > I think interpreting this to be ranges B4:C5 on Sheets 1 and 3 but all
> > > of sheet 2 does _not_ fit into the normal understanding of "cube".
> > 
> > Why all of sheet 2? 'Sheet1'.B4:'Sheet3'.C5 says cube from top left
> > front 'Sheet1'.B4 to bottom right back 'Sheet3'.C5, which in this case
> > encompasses 'Sheet2'.B4:C5, but nothing else of sheet 2. Do we have some
> > misunderstanding here?
> That's what I understood from Erics message as of 25 Nov 2008 05:16:43
> -0000 (Mon, 22:16 MST):
> > Excel, for example will sum the cells in the specified workbooks AND
> > all cells in sheets that are positioned between the 2 sheets.
> Note the "all cells" part.

I think what he meant are all cells delimited by the Z-axis edges
resulting from the corners, cube-wise..

I tried using Excel2007 with =SUM(Sheet1:Sheet3!B3:C4) and no other
cells of Sheet2 in between are evaluated. It looks like Calc and Excel
have the same understanding of a 3D reference. I would had been
surprised if that wasn't the case.

> In his next message he reiterates that this
> is different from the 'Sheet1'.B4:'Sheet3'.C5 version.

That is indeed different, it doesn't work with different sheets. Or
there are some special semantics I'm missing. IMHO that involves the ':'
range operator (as opposed to Sheet1:Sheet3!B3:C4 that probably gets
treated as one entity, I don't know) and it looks as if the range
operator doesn't handle 3D. It also doesn't if you define two names,
NAME1 to $Sheet1!$B$3 and NAME2 to $Sheet3!$C$4, and then use it as in

Calc does handle that as a 3D reference.

> It could easily be that I misunderstood but I thought it clear that he
> said there are 2 types of 3d references involved.

Maybe the range operator is the difference. Eric should be able to clarify.


Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.

PGP signature

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]