Calculating Geometric Mean in Access

[dt2demar]

How can the geometric mean of a field be calculated in Access?

The 'mean' is an aggregate function and is easy to calculate, but what about the geometric mean?

Thanks in advance.

 

[james2308]

What exactly is the geometric mean? If it is a calculation, i am sure it can be recreated if it is not a standard function.

Jay.

 

[dt2demar]

The mean is calculated as:

mean = (sum of x)/n

geo mean = sqrt(sum of squares of x)

 

[tlbroadbent]

There is no geometric mean function as far as I can determine. However, you should be able to calculate the GM using existing functions and aggregates. I beleive the geometric mean can be calculated from the arithmetic mean of the log transformed data.

SELECT Exp(Avg(Log([val1]))) AS GeoMean FROM table1; Terry

X-) "I don't have a solution, but I admire your problem."

 

[james2308]

I have had a look at this, and to actually calculate the Geometric Mean isn't too difficult. The figures it comes out with look pretty weord though!

If you would like me to send you my example, please just let me know.

James.

 

[tlbroadbent]

James,

Why not post your example for all of us to see?

Thanks, Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[james2308]

Terry,

How do I do that?

I'm new to this site, clearly!

James

 

[tlbroadbent]

Thought I would give a little more explanation and proof of the solution that I provided earlier.

The geometric mean (GM) of numbers a_1, a_2, ..., a_n is the nth root of the product a_1*a_2...*a_n

If we have 2 numbers, a and b, then GM = a*b^1/2.
Example: 4 and 9 4*9=36 36^1/2=6.

If we have 3 numbers, a, b and c, then GM = a*b*c^1/3 and so on.

This kind of calculation is difficult if not impossible over a set of data in a table using SQL statements. However, by using log transformations, we can simplify the math enabling us to create a query that will work. Products of a series of numbers can be transformed to sums of the logs of the numbers. Finding the nth root of the numbers can be transformed to division by n.

Thus a_1*a_2...*a_n^1/n can be transformed to (log(a_1)+log(a_2)...+log(a_n))/n or in SQL avg(log(colA)). Once we've found that result, we find the antilog using the exp function and thus GM=exp(avg(log(colA))).

Hope this helps. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[MichaelRed]

Terry,

Neat soloution. Many will be confused by the use of "Log". It is difficult to rember that it really meane "Ln" from math. I (almost?) always have to check before using - and I have been using it (albiet occassionally) for almost ten years.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

James,

If you have some code or a SQL statement, you can copy to the clipboard and paste it in the forum message window. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[james2308]

This is what i came up with. It doesn't seem as impressive as the stuff from Terry, and it is probabaly wrong, but here we go anyway!


SELECT tblFigures.ItemID, tblFigures.ItemName, tblFigures.[1stFigure], tblFigures.[2ndFigure], tblFigures.[3rdFigure], tblFigures.[4thFigure], Sqr(([1stFigure]*[1stFigure])+([2ndFigure]*[2ndFigure])+([3rdFigure]*[3rdFigure])+([4thFigure]*[4thFigure])) AS GeoMean
FROM tblFigures;

James

 

[tlbroadbent]

James,

I see what you are doing. Not knowing the table structure and given the definition of geometric mean as "sqrt(sum of squares of x)" you did well.

Besides the difference in definition, what your query lacks is aggregation of values. That is the tricky part of the query and the reason I had to use log transformations. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[james2308]

Terry,

thanks. This is why I learnt to make sure users write down EXACTLY what they want before i start their Access/excel project! It is really easy to lose the overall direction when you are dealing with the minutae.

I liek the ones with no calculations best!

James.