Passing an unkown range to a function

[Newbie3333]

I am sure this question has been answered before, but I have bee
looking for almost 3 hours in this forum without any luck.

What do I do when I don't know in advance the address of a column o
numbers for which I want to, say,
compute the mean.

The code below doesn't work.

Function Mean(ParamArray Pipun() As Variant) As Double
Dim pipun2 As Variant
pipun2 = Pipun()
Mean = Application.WorksheetFunction.Sum(pipun2)
End Function

(Please don't answer, use average() in Excel. )


Thanks in advance,

Newbi

 

[Kevin Beckham]

The following code will compute the mean of any range of
values, including non-contiguous, and multi-area

Function myMean(rng As Range) As Double
Dim sum As Double
Dim num As Long
Dim a As Range
Dim c As Range

sum = 0# : num = 0
For Each a in rng.Areas
For Each c In a
If IsNumber(c) Then
sum = sum + c.Value
num = num + 1
End If
Next c
Next a
If num > 0 Then myMean = sum / n Else myMean = 0
End Function

-----Original Message-----

I am sure this question has been answered before, but I have been
looking for almost 3 hours in this forum without any luck.

What do I do when I don't know in advance the address of a column of
numbers for which I want to, say,
compute the mean.

The code below doesn't work.

Function Mean(ParamArray Pipun() As Variant) As Double
Dim pipun2 As Variant
pipun2 = Pipun()
Mean = Application.WorksheetFunction.Sum(pipun2)
End Function

(Please don't answer, use average() in Excel. )


Thanks in advance,

Newbie


------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

 

[Tom Ogilvy]

or

Function Mean(Pipun As Range) As Double
Mean = Application.WorksheetFunction.Sum(pipun)
End Function

would give the sum for the result of mean as the original function shows.
Obviously replacing Sum with Average would give the Arithmetic Mean.

So the question to the OP, is why you chose to use a parameter array and
what is the real technical problem you are trying to solve. A parameter
array is used when you will have multiple arguments, but you speak about a
column of numbers (a single range - single argument).

--
Regards,
Tom Ogilvy

The following code will compute the mean of any range of
values, including non-contiguous, and multi-area

Function myMean(rng As Range) As Double
Dim sum As Double
Dim num As Long
Dim a As Range
Dim c As Range

sum = 0# : num = 0
For Each a in rng.Areas
For Each c In a
If IsNumber(c) Then
sum = sum + c.Value
num = num + 1
End If
Next c
Next a
If num > 0 Then myMean = sum / n Else myMean = 0
End Function

-----Original Message-----

I am sure this question has been answered before, but I have been
looking for almost 3 hours in this forum without any luck.

What do I do when I don't know in advance the address of a column of
numbers for which I want to, say,
compute the mean.

The code below doesn't work.

Function Mean(ParamArray Pipun() As Variant) As Double
Dim pipun2 As Variant
pipun2 = Pipun()
Mean = Application.WorksheetFunction.Sum(pipun2)
End Function

(Please don't answer, use average() in Excel. )


Thanks in advance,

Newbie


------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

.

 

[Newbie3333]

Kevin, Tom:
Thanks a lot. Why was I playing with ParamArray, etc. Sheer
ignorance!!

Newbie