How to write a VBA-Excel code to find geometric mean?

[lush888222]

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:

[VBA]
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
[/VBA]

[mikerickson]

Have you looked at the spreadsheet function GEOMEAN?

[lush888222]

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]

Please read this 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]

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.