Averages

Posted by Charles on August 29, 2001 1:27 PM

I am trying to calculate an average of numbers summed in one cell. For example, I enter =10+20+30 into a cell so that the sum of 60 shows. I would like for the next cell over to show the average of the items entered into the first cell (average = 20). Is there a way to have Excel calculate this average? Alternatively, is there a function that counts the number of items entered in a particular cell, that can be used as the divisor in a formula calculating the average?

Thanks

Posted by Helmut on August 29, 2001 5:19 PM

Here's one way, but it's not very elegant. Perhaps someone can do a UDF for you.
The macros are based upon your formulas being in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Call Calculate_Average
Application.EnableEvents = True
End Sub

Sub Calculate_Average()
Dim data As Range, x As Long
Set data = Columns("A")
x = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
With data
.Insert
.Copy
.Offset(0, -1).PasteSpecial Paste:=xlFormulas
.Offset(0, -1).Replace What:="=", _
Replacement:="""=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
End With
With Intersect(data.Offset(0, 1), ActiveSheet.UsedRange)
.FormulaR1C1 = "=RC[-1]/(LEN(RC[-2])-LEN(SUBSTITUTE(RC[-2],""+"",""""))+1)"
.Copy
.PasteSpecial Paste:=xlValues
End With
data.Offset(0, -1).Delete
End Sub

Posted by Helmut on August 29, 2001 5:22 PM

Amendment ....

Correction to first macro :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Call Calculate_Average
Application.EnableEvents = True
End If
End Sub