Geometric Mean 2

[LisaRR]

Is there a geometric mean function in Crystal? If not, does anyone have a suggestion for how to calculate?

I am using v8.5.

 

[Turkbear]

Hi,
Probably not built-in but maybe you can create a formula using the info here:

http://en.wikipedia.org/wiki/Geometric_mean

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Based on a quick read of the above link, if you are calculating based on values in one field, you could try the following formulas:

//{@initialize} to be placed in the report header:
whileprintingrecords;
numbervar x := 1;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar x := x * {table.qty};
sqr(x)

This would give you a running square root of the accumulated product. Or you could place a formula in the group or report footer:

whileprintingrecords;
numbervar x;
sqr(x)

If in a group footer, you'd need to place the initializing formula in the group header.

-LB

 

[Turkbear]

Hi,
For a true geometric mean, the root of the product varies by the number of values used to derive the product:

If 2 the Square root
3 needs cube root
4 needs fourth root , etc

Makes it difficult, given Crystal's operators to derive the geometric mean for any set that is not a multiple of 2 ( since you can take the square root of the square root to get 4th, and so forth)








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[chelseatech]

There si a slight error in the formula above. LBass assumes there are only two numbers in the data. A geometric mean is the nth root of the number.

To calculate the nth root of a number use the exponentiation operator with a fractional exponent.

So Formulas should read:
//{@initialize} to be placed in the report header
whileprintingrecords;
global numbervar x := 1; //Product variable
global numbervar c:=0; //counter variable

//{@accum} to be placed in the detail section:
whileprintingrecords;
global numbervar x := x * {table.qty};
global numbervar c:=c + 1;

Result formula in the group or report footer:
whileprintingrecords;
global numbervar x;
global numbervar c;
if c=0 then 0 else x ^ (1/c)

Thanks for an interesting problem and solution. I'd like to include that in the next issue of Crystal Clear.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

Sorry about that--I should have added a disclaimer that I don't really know anything about geometric means!

-LB

 

[LisaRR]

Chelseatech: Thank you for your post. I created formulas as directed. The result formula is creating a "numeric overflow" error. Do you know what is causing it or how to correct it?

-LR

 

[Turkbear]

Hi,
Thanks Chelseatech,
I forgot abpout using fractional exponents to derive roots..

Deserves a star.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[chelseatech]

How many numbers are you trying to multiple together and how big are they?

Could be you are exceeding a field value limit. I'm not sure what the biggest number you can use inside Crystal. With integers us old fashioned programmers always knew you couldn't go above 32767. I assume a number is double, so not sure if thats a 32 bit or 64 bit double number.

Can't see anyway around that. Try a smaller set of records and see if that is the cause of the problem.

How big are your source numbers. You could always create a normal scale and (for example) divide by 1000 on each product, and then multiple the result by 1000

Modified formauls would then read..
//{@accum} to be placed in the detail section:
whileprintingrecords;
global numbervar x := x * {table.qty} /1000;
global numbervar c := c + 1;

Result formula in the group or report footer:
whileprintingrecords;
global numbervar x;
global numbervar c;
if c=0 then 0 else 1000 * (x ^ (1/c))

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[LisaRR]

I am trying to calculate a monthly and period to date geo mean for a data set of about 400 records / month. Field values range from 0 to 30. If I run one month at a time, your formula works - I validated it against the geo mean formula in excel. I need to run more than one month at a time because I need year to date values. I tried your suggestion to divide then multiply by 1000 but the formula result is all zeroes. I think we are getting close, thank you for your expertise - it is much appreciated!

 

[chelseatech]

A zero in your data will mess everything up. If your values are between 0 and 30 normalise them to that range as below. Also note that genometric mean can't work with negative numbers so my formula below excludes those too.

//{@accum} to be placed in the detail section:
whileprintingrecords;
global numbervar x;
global numbervar c;
If {table.qty}> 0 then (
x:= x * {table.qty} /30;
c:= c + 1

Result formula in the group or report footer:
whileprintingrecords;global numbervar x;
global numbervar c;if c=0 then 0 else 30 * (x ^ (1/c))

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[LisaRR]

Chelseatech - Thanks for your input on this formula. I tried your latest modifications (coppied and pasted but changed the field name) but still end up with 0.0000000 for the final result. At this point, I think I will just export my values to excel and get the geomean there.

Your original formulas do work accurately for data sets up to about 400 records. Normalising seems to cause a problem with the final result.

Thank you again, I appreciate your time.

LR

 

[chelseatech]

Found another solution to the overflow problem. Use a log value and then the results can be added instead of multiplied. A little trickier to convert the log back into a number, but I sorted that out too.

The formulas are on my BLOG entry for today at

http://www.crystalkiwi.com/blog.htm

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[LisaRR]

Fantastic! It works, and I validated the result using the GeoMean formula in Excel. Thank you so much, this has saved me a lot of time and reduced the potential for error in my calculations.

You should be TipMaster of the week just for solving this one!!!!