Passing an unkown range to a function

  • Thread starter Thread starter Newbie3333
  • Start date Start date
N

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
 
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


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
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).
 
Kevin, Tom:
Thanks a lot. Why was I playing with ParamArray, etc. Sheer
ignorance!!

Newbie
 
Back
Top