PDA

View Full Version : How to write a VBA-Excel code to find geometric mean?



lush888222
07-19-2008, 09:30 PM
Hi,
geometric mean is assume you have the following 2 numbers in a excel column: 2, 8
geometric mean = (2*8)^(1/n)
n = 2, since there are 2 numbers, 2 and 8.
so, geometric mean = (2*8)^(1/2)=16^(1/2) = 4

geometric mean = nth root of multiple of n numbers.

so I have to write a simple VBA-excel code/function to find the geometric mean of any set of numbers in an excel column.

I write a code but it doesn't give me the correct answers, could you please help me to correct it?

Here it goes:


Option Explicit
Function Geometric(rs)
Dim Sum as single
Dim i As Integer
Dim n As Integer
n = rs.Count
For i = 1 To n
sum = sum + (rs(i)) ^ (1 / n)
Next i
Geometric = sum
End Function

mikerickson
07-19-2008, 09:43 PM
Have you looked at the spreadsheet function GEOMEAN?

lush888222
07-19-2008, 09:58 PM
yes I know,
But I have to write a whole new function that does the same work as geomean function. Because I am attending a VBA class.
so I need somebody to help me correcting my VBA function code.

mdmackillop
07-20-2008, 03:37 AM
Please read this (http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_hom_faq_item)regarding homework questions.

Your problem as I see it is not per se the VBA code. It produces a solution, albeit the wrong one. Your logic in the code is the problem.
For a four item mean, the code would be eg
=(2x3x4x5) ^ (1/4)
Where, in this, would the + sign in your code apply?
Do it in two stages. Get the first result correct (multiplications), then apply the exponential.
A hint: Initialise Sum

Bob Phillips
07-20-2008, 03:54 AM
You also need to cater for cells that are not numeric or are empty, and this affects both the sum and the value of n in calculation.