mean and variance

I want to calculate a mean and a variance, but when mean() is called
there is a type mismatch. why?

Function Mean(arr As Range)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To ran.Rows.Count
Sum = Sum + ran.Cells(1, i)
Next i

Mean = Sum / ran.Rows.Count
End Function

Function StdDev(arr As Range)
Dim i As Integer
Dim avg As Single, SumSq As Single

avg = Mean(arr)
For i = 1 To ran.Rows.Count
SumSq = SumSq + (ran.Cells(1, i) - avg) ^ 2
Next i

StdDev = Sqr(SumSq / (ran.Rows.Count - 1))
End Function

Sub MeanVar()

Dim ran As Range
Dim s, Mean, var, num As Integer


Set ran = Application.InputBox("in which range is the variable",
Type:=8)

M = Mean(ran)
s = StdDev(ran)

ran.Offset(1) = Mean
ran.Offset(2) = s

End Sub

Your functions have an "arr" parameter, but you use undefined variable "ran"
instead.

HTH
--
AP

"Dirk" <dirknbr@googlemail.com> a écrit dans le message de
news:1141917017.144231.105290@v46g2000cwv.googlegroups.com...
> I want to calculate a mean and a variance, but when mean() is called
> there is a type mismatch. why?
>
> Function Mean(arr As Range)
> Dim Sum As Single
> Dim i As Integer
>
> Sum = 0
> For i = 1 To ran.Rows.Count
> Sum = Sum + ran.Cells(1, i)
> Next i
>
> Mean = Sum / ran.Rows.Count
> End Function
>
> Function StdDev(arr As Range)
> Dim i As Integer
> Dim avg As Single, SumSq As Single
>
> avg = Mean(arr)
> For i = 1 To ran.Rows.Count
> SumSq = SumSq + (ran.Cells(1, i) - avg) ^ 2
> Next i
>
> StdDev = Sqr(SumSq / (ran.Rows.Count - 1))
> End Function
>
> Sub MeanVar()
>
> Dim ran As Range
> Dim s, Mean, var, num As Integer
>
>
> Set ran = Application.InputBox("in which range is the variable",
> Type:=8)
>
> M = Mean(ran)
> s = StdDev(ran)
>
> ran.Offset(1) = Mean
> ran.Offset(2) = s
>
> End Sub
>

Dirk,
First you declare MEAN as a variable when it also the name of
your function so change one or the other.

Other points:

Functions parameter is ARR but your loop processes RAN - change to ARR. I
also advice you change your SINGLE declarations to DOUBLE.

HTH

"Dirk" wrote:

> I want to calculate a mean and a variance, but when mean() is called
> there is a type mismatch. why?
>
> Function Mean(arr As Range)
> Dim Sum As Single
> Dim i As Integer
>
> Sum = 0
> For i = 1 To ran.Rows.Count
> Sum = Sum + ran.Cells(1, i)
> Next i
>
> Mean = Sum / ran.Rows.Count
> End Function
>
> Function StdDev(arr As Range)
> Dim i As Integer
> Dim avg As Single, SumSq As Single
>
> avg = Mean(arr)
> For i = 1 To ran.Rows.Count
> SumSq = SumSq + (ran.Cells(1, i) - avg) ^ 2
> Next i
>
> StdDev = Sqr(SumSq / (ran.Rows.Count - 1))
> End Function
>
> Sub MeanVar()
>
> Dim ran As Range
> Dim s, Mean, var, num As Integer
>
>
> Set ran = Application.InputBox("in which range is the variable",
> Type:=8)
>
> M = Mean(ran)
> s = StdDev(ran)
>
> ran.Offset(1) = Mean
> ran.Offset(2) = s
>
> End Sub
>
>