Average Value

  • Thread starter Thread starter Saxman
  • Start date Start date
S

Saxman

I have a column with the following numbers. How can I award the values
-,0, , with the average of each sequence? Note there are three
sequences. In reality the column would be much longer with more sequences.

73
73
72
58
56
49
44
23
0
-
-
79
78
77
77
54
53
51
48
44
33
21
20
0
-
-
100
99
54
53
51
47
42
41
33
30
0
-
-
 
Hi John,

Am Thu, 28 Aug 2014 15:25:06 +0100 schrieb Saxman:
I have a column with the following numbers. How can I award the values
-,0, , with the average of each sequence? Note there are three
sequences. In reality the column would be much longer with more sequences.
Click to expand...

do it with a macro:

Sub myAvg()
Dim LRow As Long, i As Long
Dim Start As Long, Ende As Long
Dim c As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow
Set c = .Range("A1:A" & LRow).Find(0, after:=.Cells(i, 1), _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Ende = c.Row
c.Offset(, 1) = WorksheetFunction.Average _
(.Range(.Cells(i, 1), .Cells(Ende, 1)))
i = Ende + 3
End If
Next
End With

End Sub

This macro writes the average of each sequence in column B if A = 0.
If the 0 should not be included in the average then change this line:
c.Offset(, 1) = WorksheetFunction.Average _
(.Range(.Cells(i, 1), .Cells(Ende-1, 1)))
^^^^^


Regards
Claus B.
 
Hi John,

Am Thu, 28 Aug 2014 23:05:36 +0100 schrieb Saxman:
I need the average for the values, - and blank
(nothing)?
Click to expand...

Average ignores blanks and text.
Therefore you have to replace blanks or hyphens with 0
Where does a sequence end? At the first 0 or at first new value?
Has the first sequence the hyphens included or belong these hyphens to
sequence2?


Regards
Claus B.
 
Hi John,

Am Thu, 28 Aug 2014 23:05:36 +0100 schrieb Saxman:


Average ignores blanks and text.
Therefore you have to replace blanks or hyphens with 0
Where does a sequence end? At the first 0 or at first new value?
Has the first sequence the hyphens included or belong these hyphens to
sequence2?


Regards
Claus B.
Click to expand...


Hyphens and blanks could be replaced with a Find/Replace. They are
horse ratings. Every horse needs to be given a value if they are in a
horserace. (Some horses might have a missing value as it has not ran
before or they might have come from abroad). The average of the race is
a compromise.

The sequence ends where the values change from low to high (hence the sort).

Values would need to be sorted in rating order beforehand.
 
Hi John,

Am Fri, 29 Aug 2014 09:04:23 +0100 schrieb Saxman:
Hyphens and blanks could be replaced with a Find/Replace. They are
horse ratings. Every horse needs to be given a value if they are in a
horserace. (Some horses might have a missing value as it has not ran
before or they might have come from abroad). The average of the race is
a compromise.
Click to expand...

let the hyphens remain in the cells and try the formula
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
in workbook "Average"


Regards
Claus B.
 
Hi John,

Am Fri, 29 Aug 2014 10:03:09 +0100 schrieb Saxman:
Could the blanks, zeros and hyphens be replaced with the average result
in order to save doing it manually?
Click to expand...

in this case you have to do it with VBA. Write under the last value in
column A "End" and try this code:

Sub myAvg()
Dim LRow As Long, i As Long
Dim Start As Long
Dim rngC As Range
Dim dblAvg As Double

With ActiveSheet
.Range("A:A").Replace what:="-", replacement:=""
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Start = 1
For i = Start To LRow
If .Cells(i + 1, 1) > .Cells(i, 1) Then
dblAvg = WorksheetFunction.Sum(.Range(.Cells(Start, 1), _
.Cells(i, 1))) / (i + 1 - Start)
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:=0, replacement:=dblAvg, lookat:=xlWhole
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:="", replacement:=dblAvg, lookat:=xlWhole
Start = i + 1
End If
Next
End With

End Sub

Or look again in OneDrive for "Average" and download the workbook


Regards
Claus B.
 
Hi John,

Am Fri, 29 Aug 2014 10:03:09 +0100 schrieb Saxman:


in this case you have to do it with VBA. Write under the last value in
column A "End" and try this code:

Sub myAvg()
Dim LRow As Long, i As Long
Dim Start As Long
Dim rngC As Range
Dim dblAvg As Double

With ActiveSheet
.Range("A:A").Replace what:="-", replacement:=""
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Start = 1
For i = Start To LRow
If .Cells(i + 1, 1) > .Cells(i, 1) Then
dblAvg = WorksheetFunction.Sum(.Range(.Cells(Start, 1), _
.Cells(i, 1))) / (i + 1 - Start)
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:=0, replacement:=dblAvg, lookat:=xlWhole
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:="", replacement:=dblAvg, lookat:=xlWhole
Start = i + 1
End If
Next
End With

End Sub

Or look again in OneDrive for "Average" and download the workbook
Click to expand...

Your spreadsheet code works fine, but are you sure with the average
calculation? My calculations differ doing it manually.

The first sequence averages out at 56 and yet 40.72 has been awarded for
the blanks and so forth.

Thank you.
 
Hi John,

Am Fri, 29 Aug 2014 13:43:52 +0100 schrieb Saxman:
Your spreadsheet code works fine, but are you sure with the average
calculation? My calculations differ doing it manually.
Click to expand...

how do you calculate the average?
The first sequence has a sum of 448 and 11 rows with the 0
and the 2 hyphen.
448/11 = 40,7272727272727


Regards
Claus B.
 
Hi John,



Am Fri, 29 Aug 2014 13:43:52 +0100 schrieb Saxman:







how do you calculate the average?

The first sequence has a sum of 448 and 11 rows with the 0

and the 2 hyphen.

448/11 = 40,7272727272727





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

Sorry about the delay. There is trouble with the server.

You are correct if one quotes the number of samples (11), giving 40.72. However, if one awards the average of those that have a rating, the answer is 56 to those that do not have a rating?
 
Hi John,

Am Freitag, 29. August 2014 16:39:58 UTC+2 schrieb John Williams:
You are correct if one quotes the number of samples (11), giving 40.72. However, if one awards the average of those that have a rating, the answer is 56 to those that do not have a rating?
Click to expand...

my server is also down.
I guess I misunderstand your Problem because of my bad English.
What is your expected result for the sequences? Should that result go to all blank cells and all cells with 0 in it?

Kind Regards
Claus
 
Hi John,



Am Freitag, 29. August 2014 16:39:58 UTC+2 schrieb John Williams:






my server is also down.

I guess I misunderstand your Problem because of my bad English.

What is your expected result for the sequences? Should that result go to all blank cells and all cells with 0 in it?
Click to expand...

I have had a good look at the relevant data. The following non-numerical entries (4) are the following (including zero (0)). I'm noting this because the hyphen (dash) is aligned differently sometimes. Hopefully, it is the same ASCII character?

-
-
(Blank)
0

The average of those with a rating should replace those as immediately above.

For instance:

89
82
68
49
45
37
30
20
-
-

-
0

Should appear as:

89
82
68
49
45
37
30
20
52.5
52.5
52.5
52.5
52.5
 
Hi John,

Am Fri, 29 Aug 2014 09:46:41 -0700 (PDT) schrieb John Williams:
89
82
68
49
45
37
30
20
-
-

-
0

Should appear as:

89
82
68
49
45
37
30
20
52.5
52.5
52.5
52.5
52.5
Click to expand...

then try:

Sub myAvg()
Dim LRow As Long, i As Long
Dim Start As Long
Dim rngC As Range
Dim dblAvg As Double

With ActiveSheet
.Range("A:A").Replace what:="-", replacement:=""
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Start = 1
For i = Start To LRow
If .Cells(i + 1, 1) > .Cells(i, 1) Then
dblAvg = WorksheetFunction.AverageIf(.Range(.Cells(Start, 1), _
.Cells(i, 1)), ">0", .Range(.Cells(Start, 1), .Cells(i, 1)))
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:=0, replacement:=dblAvg, lookat:=xlWhole
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:="", replacement:=dblAvg, lookat:=xlWhole
Start = i + 1
End If
Next
End With

End Sub


Regards
Claus B.
 
Hi John,

Am Fri, 29 Aug 2014 09:46:41 -0700 (PDT) schrieb John Williams:


then try:

Sub myAvg()
Dim LRow As Long, i As Long
Dim Start As Long
Dim rngC As Range
Dim dblAvg As Double

With ActiveSheet
.Range("A:A").Replace what:="-", replacement:=""
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Start = 1
For i = Start To LRow
If .Cells(i + 1, 1) > .Cells(i, 1) Then
dblAvg = WorksheetFunction.AverageIf(.Range(.Cells(Start, 1), _
.Cells(i, 1)), ">0", .Range(.Cells(Start, 1), .Cells(i, 1)))
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:=0, replacement:=dblAvg, lookat:=xlWhole
.Range(.Cells(Start, 1), .Cells(i, 1)).Replace _
what:="", replacement:=dblAvg, lookat:=xlWhole
Start = i + 1
End If
Next
End With

End Sub
Click to expand...

That works fine Claus.

Could the code be modified so that the return data was formatted to zero
(0) decimal places?

TIA
 
Hi John,

Am Fri, 29 Aug 2014 18:13:58 +0100 schrieb Saxman:
Could the code be modified so that the return data was formatted to zero
(0) decimal places?
Click to expand...

insert that line at the end of the code between Next and End With:
Next
.Range("A1:A" & LRow).NumberFormat = "0"
End With


Regards
Claus B.
 
Hi John,

Am Fri, 29 Aug 2014 18:13:58 +0100 schrieb Saxman:


insert that line at the end of the code between Next and End With:
Next
.Range("A1:A" & LRow).NumberFormat = "0"
End With
Click to expand...

That works fine Claus.

Thanks very much for you valued help and knowledge.
 
That works fine Claus.

Thanks very much for you valued help and knowledge.
Click to expand...


Just one more thing Claus. Could the code be amended for multiple columns?

Ideally, these are the columns to include.

X2:X
Z2:Z
AA2:AA
AB2:AB
AC2:AC
AD2:AD
AE2:AD
AK2:AK
AL2:AL
AM2:AM
AN2:AN
AO2:AO
AP2:AP
AQ2:AQ
AX2:AX
 
Hi John,

Am Sat, 30 Aug 2014 08:42:19 +0100 schrieb Saxman:
Ideally, these are the columns to include.

X2:X
Z2:Z
AA2:AA
AB2:AB
AC2:AC
AD2:AD
AE2:AD
AK2:AK
AL2:AL
AM2:AM
AN2:AN
AO2:AO
AP2:AP
AQ2:AQ
AX2:AX
Click to expand...

try:

Sub myAvg()
Dim LRow As Long, i As Long, n As Long
Dim Start As Long
Dim rngC As Range
Dim dblAvg As Double
Dim strCol As String, arrCol As Variant

strCol = "X,Z,AA,AB,AC,AD,AE,AK,AL,AM,AN,AO,AP,AQ,AX"
arrCol = Split(strCol, ",")

With ActiveSheet
For n = LBound(arrCol) To UBound(arrCol)
LRow = .Cells(Rows.Count, arrCol(n)).End(xlUp).Row
.Range(.Cells(2, arrCol(n)), .Cells(LRow, arrCol(n))) _
.Replace what:="-", replacement:=""
.Cells(LRow + 1, arrCol(n)) = "End"
Start = 2
For i = Start To LRow
If .Cells(i + 1, arrCol(n)) > .Cells(i, arrCol(n)) Then
dblAvg = WorksheetFunction.AverageIf(.Range(.Cells(Start,
arrCol(n)), _
.Cells(i, arrCol(n))), ">0", .Range(.Cells(Start,
arrCol(n)), _
.Cells(i, arrCol(n))))
.Range(.Cells(Start, arrCol(n)), .Cells(i,
arrCol(n))).Replace _
what:=0, replacement:=dblAvg, lookat:=xlWhole
.Range(.Cells(Start, arrCol(n)), .Cells(i,
arrCol(n))).Replace _
what:="", replacement:=dblAvg, lookat:=xlWhole
Start = i + 1
End If
Next
.Range(.Cells(2, arrCol(n)), .Cells(LRow, arrCol(n))).NumberFormat
= "0"
Next
End With

End Sub


Regards
Claus B.
 
Back
Top