Calculating Geometric Mean

[efinnen]

Hey Folk<br>
<br>
Well I'm beginning to lose some patience at Access. I guess we all have at some point in time though, right??<br>
<br>
Anyways Question..<br>
<br>
I need to calculate the geometric mean of a set of numbers and I'm having a brainfart on how to do it programatically. Anyone have any advice???<br>
<br>
Access help shows the GEOMEAN function, but methinks it's an Excel function that they just throw in there to give me a headache..<br>
<br>
Question 2 While I'm at it. Anyone know how to open Excel (from Access) perform an Excel function and return the value to Acess??<br>
<br>
Thanks and I appreciate the help.<br>
Eric<br>
FYI : Geomean function for say 5 values (n1,n2....n5)<br>
(n1*n2*n3*n4*n5)^(1/5)<br>
<br>

 

[DougP]

So write your own<br>
You know the formula<br>
<br>
Click on the Modules TAB<br>
click New if you don't have one already <br>
Else click Design<br>
Then with the code window open<br>
click the &quot;Insert menu&quot;<br>
Click &quot;Procedure&quot;<br>
Type in your &quot;Function&quot; name<br>
and make sure the function button below is highlighted<br>
then you will see the following: <br>
<br>
Public Function Mean1()<br>
<br>
End Function<br>
<br>
I called mine Mean1 for this example<br>
<br>
Now inside the parenthesis put your varaibles to pass to your funcion<br>
I'll have to beg ignorance here cause I' don't know your function.<br>
But it looks like this if you have four items to pass<br>
<br>
Public Function Mean1(A,b,c,d)<br>
<br>
<br>
End Function<br>
Now inside you do your calculaitons<br>
<br>
then right before the End Function pass the final answer back to the function name like this<br>
Mean1 = MyFinalAnswer like so<br>
<br>
Public Function Mean1(A,b,c,d)<br>
'blah blah<br>
'calulate to the hilt<br>
Mean1 = MyFinalAnswer <br>
End Function<br>
<br>
Ok you can do it<br>
<br>
Access can now do anything your imagination desires<br>
<br>
Now to call your groovey function<br>
use this<br>
=Mean1(MyVal1,MyVal2,MyotherValue,AndSomeMore)<br>
You can pass Fields from a recordsource or other fields from your query or report.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>