函数:返回均方差,并能判断单元格为空
先插入模块,然后再编写代码
Function AverageRange(startCell As Range, endCell As Range) As Double
Dim rng As Range
Dim sum As Double
Dim avg As Double
Dim std_err As Double
Dim count As Integer
Set rng = Range(startCell, endCell)
sum = 0
count = 0
For Each cell In rng
If Not IsEmpty(cell) Then
sum = sum + cell.Value
count = count + 1
End If
Next cell
If count > 0 Then
avg = sum / count
sum = 0
For Each cell In rng
If Not IsEmpty(cell) Then
sum = sum + (cell.Value - avg) ^ 2
End If
Next cell
AverageRange = Sqr(sum / count)
Else
avg = 0
End If
End Function