Paula has attended:
Excel VBA Intermediate course
Worksheetfunction.average with empty values
Hi,
I am trying to do an average of a range of values. Each value is taken from a different cell. It might be that some of the cells are empty, then the value is filled with "".
In that cases I get an error saying: Unable to get the average property of the worksheet function
However, if I calculate such average in a worksheet cell it works fine.
Here is part of the code:
Dim dSat_act(0 To 4) As Variant
Dim j As Integer
Dim i As Integer
Dim dAverage As Double
Range("L7").Select
j = 0
For i = 10 To 1 Step -2
ActiveCell.Offset(0, -i).Select
If ActiveCell.Value <> 0 Then
dSat_act(j) = ActiveCell
Else
dSat_act(j) = ""
End If
ActiveCell.Offset(0, i - 1).Select
j = j + 1
Next i
dAverage = Application.WorksheetFunction.Average(dSat_act(0), dSat_act(1), dSat_act(2), dSat_act(3), dSat_act(4))
Range("L7").Value = dAverage
RE: worksheetfunction.average with empty values
Hi Paula,
Thank you for the forum question.
"" is a text string. Average cannot be calculated if you have a text string in the array.
I have changed your code. I have removed the
Else
dSat_act(j) = "" to avoid text in the array.
I have also changed
dAverage = Application.WorksheetFunction.Average(dSat_act) to only reference the array instead each part of the array. This should do what you want. Please let me know if I am wrong.
Dim dSat_act(0 To 4) As Variant
Dim j As Integer
Dim i As Integer
Dim dAverage As Double
Range("L7").Select
j = 0
For i = 10 To 1 Step -2
ActiveCell.Offset(0, -i).Select
If ActiveCell.Value <> 0 Then
dSat_act(j) = ActiveCell
End If
ActiveCell.Offset(0, i - 1).Select
j = j + 1
Next i
dAverage = Application.WorksheetFunction.Average(dSat_act)
Range("L7").Value = dAverage
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: worksheetfunction.average with empty values
Hi Jens,
thank you for your answer.
However, it does not do what I want. Since now the blank cells are treated as containing a "0" value, thus being considered to calculate the average value. In the mean time I have found a way around it:
Dim dSat_act(0 To 4) As Double
Dim j As Integer
Dim i As Integer
Dim dAverage As Double
Dim dSum As Double
Dim bCount As Byte
Range("L7").Select
j = 0
For i = 10 To 1 Step -2
ActiveCell.Offset(0, -i).Select
dSat_act(j) = ActiveCell
ActiveCell.Offset(0, i - 1).Select
j = j + 1
Next i
For j = 0 To 4
If dSat_act(j) <> 0 Then
dSum = dSum + dSat_act(j)
bCount = bCount + 1
End If
Next j
dAverage = dSum / bCount
Range("L7").Value = dAverage
paula
RE: worksheetfunction.average with empty values
Hi Paula,
I am glad you found a solution. I was a little afraid that Excel would treat it as 0. I tried to found a way for Excel to store #N/A in the array, but I couldn't.
When I tested the code I suggested, it didn't tread the empty (blank) input as 0, but again I didn't loop through the cells and may be this made the different.
Very nice work I like your logic and solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
