Arrays in iWork (Numbers)

The first syntax
=SUMPRODUCT(A1:A5,B1:B5)
behaves perfectly.

Of course, if your system is set to use the decimal comma, in the formulas you will have to suse semi-colon as parameters delimiter:

=SUMPRODUCT(A1:A5;B1:B5)

The second syntax is not accepted (there is no "array" object in Numbers).

This kind of problem is perfectly treated in "iWork Formulas and Functions User Guide" which is available for all of us from the Help menu.

Yvan KOENIG (from FRANCE dimanche 10 mai 2009 16:14:49)

(1) As far as I am able to read English, the iWork Formulas and Functions User Guide clearly states that the fonction apply to POSITIVE values.

GEOMEAN
The GEOMEAN function returns the geometric mean.
GEOMEAN(pos-num, pos-num…)
• pos-num: A positive number. pos-num is a number value and must be greater than
0.
• pos-num…: Optionally include one or more additional positive numbers.

(2) As far as I know, the mathematical object Geometric Mean obey to the same definition.

Wikipedia for instance clearly explains why:

The geometric mean only applies to positive numbers in order to avoid taking the root of a negative product, which would result in imaginary numbers, and also to satisfy certain properties about means, which is explained later in the article.

(3) Given that, it's our duty to check that the values used as parameters for teh GEOMAN function are fitting the rule.

(4) How must I write that the array structure is unavailable in Numbers?
From Numbers point of view your formula is perfectly meaningless.
As I refuse to use Microsoft products I don't know what it is supposed to do.

(5) given the Numbers rules, the internal component of the formula is odd

1+(A1:A5) calculates the sum 1 + contents of cell A1
If A1 contains 5, the result is 6
this is a number
so, GEOMEAN(1+(A1:A5) applying to this number returns the number itself
which, in my example is 6

substracting 1 returns 5.
Enclosing your formula between curly brackets forces the formula parser to identify your entry as a string so the cell will display
{=GEOMEAN(1+(A1:A5)}

I repeat, Numbers is not Excel.
To use it you must _think different._

Yvan KOENIG (from FRANCE lundi 11 mai 2009 11:16:46)

David,

That array formula does not give the same results as the straight GEOMEAN formula. Try a simple test with two numbers, say 4 and 16. The geometric mean of these is 8 but the array formula you suggested gives a result of 8.2195. With a larger data set that error gets smaller. But more importantly, at least with Excel Mac 2008, that array formula does not work for me with negative numbers.

As Yvan pointed out, the geometric mean is not defined for negative numbers and zero. Perhaps you can fool Excel into working with them but does it give you a true answer?

Hello

the OP's formula is perfectly odd.

the geomean of values +1 is not the (geoman of values)+1

the formula calculates

geoman(a1 1,a2+1,a3+1,a4+1,a51)-1
which is not the same than
geoman(a1,a2,a3,a4,a5)

It's the well known problem when spreadsheet users try to use functions without understanding what they are designed to do.

Yvan KOENIG (from FRANCE lundi 11 mai 2009 18:00:54)

It just dawned on me what David is trying to do. I believe it has to do with determining return rates on financial instruments of some kind. So, if you have growth rates of +15%, -20%, +5% (for example), you convert them to 1.15, 0.8, 1.05 by adding one. Now you take the geometric mean of those figures and subtract 1 to get a compounded rate of return.

A column is required for the "percentage+1". Excel has a method for doing the +1 within the formula, Numbers does not.

Badunit wrote:
A column is required for the "percentage+1". Excel has a method for doing the +1 within the formula, Numbers does not.

Normal, the way it's done in Excel is based on the availability of the structure "array".

Yvan KOENIG (from FRANCE lundi 11 mai 2009 18:42:46)

Warning - Novice user- could be talking b0ll0cks

David - I had a similar problem with the frequency function

excel produces an array

so does numbers - only numbers does not output the array
to read the array you need to use the index function
=index(SUMPRODUCT(A1:A5, B1:B5), 1)

the 1 at the end is for the 1st element of the array

read the help for frequency in the function browser

I repeat:

_Numbers doesn't embed the "array structure"._

Is it so difficult to understand that ?

Yvan KOENIG (from FRANCE lundi 25 mai 2009 19:38:11)

David Westwood wrote:
Conclusion: Numbers doesn't lend itself to financial analysis as Excel does!

It never claims that it was able to do that !

Yvan KOENIG (from FRANCE mardi 28 juillet 2009 17:55:37)