98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Worksheetfunction.average with empty values | Excel forum
Worksheetfunction.average with empty values | Excel forum
Resolved · Medium Priority · Version 365
Mon 16 Jul 2018, 14:34Delegate Paula said...
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
Mon 16 Jul 2018, 20:38Trainer Jens said...
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
Tue 17 Jul 2018, 08:36Delegate Paula said...
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
Tue 17 Jul 2018, 18:55Trainer Jens said...
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
Tue 24 Jul 2018: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Selecting constant values onlyIf periodically you need to change all your values back to zero, but leave formulas, text and blank cells as they are select the entire worksheet, choose F5 function key, Special and then Constants and choose the appropriate sub-selections. To enter zero in all the selected cells type 0 and then press Ctrl+Enter. |
