[Issue] Calc 3.1 RC2 AVERAGEIF() and OFFSET() support

[Anagoge]

I put this (simplified) formula in a .xlsx document:
=AVERAGE(B2:OFFSET(B2, 0, 0))

In Excel 2007 this works fine, but Calc 3.1 RC2 reports this:
Err:509 Error: Operator Missing
And improperly changes the formula to:
=AVERAGE(B2:B2(B2; 0; 0))

I assume this is a bug in the Calc support for Excel formulas?

The same goes for AverageIf/AverageIfs. If I use that in an excel .xlsx, Calc reports this:
#NAME

A sample Excel document is attached.

[I also had problems opening any Excel file containing a 3D surface plot, but it looks like those are not supported as well.]

Book1.xlsx

Sample Excel 2007 File Showing the Bugs

(8.47 KiB) Downloaded 211 times

[FJCC]

According to the Calc Help file, the OFFSET function returns the value of the cell if it points at a single cell. So your example with the AVERAGE function will not work since it will amount to =Average(B2:Val) where Val is the value of B2. The Excel OFFSET always returns a cell or range reference.

It is true that Calc does not have an AverageIfs function, at least as far as I know.

Calc is not a clone of Excel, so gaps in compatibility are to be expected. The Averageifs function looks like something appropriate for a database, if I understand it correctly. Base would be the application for that.

[Anagoge]

I reported the missing AverageIf here if someone wants to confirm/verify it:
http://www.openoffice.org/issues/show_bug.cgi?id=101466

I reported the Excel 2007 Offset() compatibility issue here:
http://www.openoffice.org/issues/show_bug.cgi?id=101467

I certainly understand that/why Calc can't do everything Excel does - but I was just hoping that it would reliably handle most/all of my existing spreadsheets.

One thing Calc does better than Excel is Sun's (beta) non-linear solver extension. The Excel solver is completely unable to handle non-linear optimizations. I can struggle through the formula issues I reported above, since I can probably code around them once I know about them all and have time to update all of my formulas. The main things keeping me from moving to Calc are the lack of surface plots and the slowness when opening, saving, and editing large spreadsheets. I'll try again with the next major release after 3.1...

Is anyone aware of an equivalent to simulate OFFSET returning a reference, instead of a value?

[Villeroy]

OFFSET takes one reference and up to 4 numbers, always returning a reference to one or more than one cell. No need to use range operator ":".
http://user.services.openoffice.org/en/ ... 625#p82625

The latest version of Excel is the only spreadsheet with COUNTIFS and SUMIFS. In my opinion it's obsolete.