| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average a changing range of values
I need to average utilisation values for server names, but the range of
values changes. Please can someone help with a macro for this. Data example: Time Duration Utilisation % Server Name CW001P01-SH-C: CW001P01-SH-C: 2009/09/19 23:56 903 96.6814438 2009/09/20 00:11 899 96.68444105 2009/09/20 00:26 901 96.68782082 2009/09/20 00:56 927 96.69387136 2009/09/20 01:12 872 96.69638761 2009/09/20 01:26 902 96.69934001 2009/09/20 01:41 898 96.70246381 CW001P03-SH-C: CW001P03-SH-C: 2009/09/19 23:56 903 99.20851502 2009/09/20 00:11 900 99.21775174 2009/09/20 00:26 901 99.22421268 2009/09/20 00:41 899 99.22657119 2009/09/20 00:56 928 99.24876246 2009/09/20 01:12 872 99.25364643 2009/09/20 01:26 902 99.2604542 CW001P03-SH-E: CW001P03-SH-E: 2009/09/19 23:56 904 32.10759256 2009/09/20 00:11 899 32.10688525 2009/09/20 00:26 901 32.10960911 2009/09/20 00:41 900 32.10492405 2009/09/20 00:56 927 32.10638105 2009/09/20 01:12 872 32.09597414 2009/09/20 01:26 902 32.09691138 Thanks, |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Average a changing range of values
One way
Sub AverageBlocks() lr = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(lr, 1) = "c" With Range("a1:a" & lr) ' Set c = .Find(2, LookIn:=xlValues) Set c = Columns("a").Find(What:="C", After:=Range("a1"), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do fr = c.Row nR = Columns("a").Find(What:="C", After:=Cells(fr, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row - 1 If nR = 1 Then Cells(lr, 1) = " " Exit Sub End If 'MsgBox fr 'MsgBox nR 'MsgBox Application.Average(Range(Cells(fr + 1, 3), Cells(nR, 3))) Cells(nR, 5) = .Application.Average(Range(Cells(fr + 1, 3), Cells(nR, 3))) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Richard" wrote in message ... I need to average utilisation values for server names, but the range of values changes. Please can someone help with a macro for this. Data example: Time Duration Utilisation % Server Name CW001P01-SH-C: CW001P01-SH-C: 2009/09/19 23:56 903 96.6814438 2009/09/20 00:11 899 96.68444105 2009/09/20 00:26 901 96.68782082 2009/09/20 00:56 927 96.69387136 2009/09/20 01:12 872 96.69638761 2009/09/20 01:26 902 96.69934001 2009/09/20 01:41 898 96.70246381 CW001P03-SH-C: CW001P03-SH-C: 2009/09/19 23:56 903 99.20851502 2009/09/20 00:11 900 99.21775174 2009/09/20 00:26 901 99.22421268 2009/09/20 00:41 899 99.22657119 2009/09/20 00:56 928 99.24876246 2009/09/20 01:12 872 99.25364643 2009/09/20 01:26 902 99.2604542 CW001P03-SH-E: CW001P03-SH-E: 2009/09/19 23:56 904 32.10759256 2009/09/20 00:11 899 32.10688525 2009/09/20 00:26 901 32.10960911 2009/09/20 00:41 900 32.10492405 2009/09/20 00:56 927 32.10638105 2009/09/20 01:12 872 32.09597414 2009/09/20 01:26 902 32.09691138 Thanks, |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Average a changing range of values
Hi Don,
Thanks a MILLION. After I added in the Dim statements your code worked like a dream. FANTASTIC!!! Thanks again. "Don Guillett" wrote: One way Sub AverageBlocks() lr = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(lr, 1) = "c" With Range("a1:a" & lr) ' Set c = .Find(2, LookIn:=xlValues) Set c = Columns("a").Find(What:="C", After:=Range("a1"), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do fr = c.Row nR = Columns("a").Find(What:="C", After:=Cells(fr, 1), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Row - 1 If nR = 1 Then Cells(lr, 1) = " " Exit Sub End If 'MsgBox fr 'MsgBox nR 'MsgBox Application.Average(Range(Cells(fr + 1, 3), Cells(nR, 3))) Cells(nR, 5) = .Application.Average(Range(Cells(fr + 1, 3), Cells(nR, 3))) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Richard" wrote in message ... I need to average utilisation values for server names, but the range of values changes. Please can someone help with a macro for this. Data example: Time Duration Utilisation % Server Name CW001P01-SH-C: CW001P01-SH-C: 2009/09/19 23:56 903 96.6814438 2009/09/20 00:11 899 96.68444105 2009/09/20 00:26 901 96.68782082 2009/09/20 00:56 927 96.69387136 2009/09/20 01:12 872 96.69638761 2009/09/20 01:26 902 96.69934001 2009/09/20 01:41 898 96.70246381 CW001P03-SH-C: CW001P03-SH-C: 2009/09/19 23:56 903 99.20851502 2009/09/20 00:11 900 99.21775174 2009/09/20 00:26 901 99.22421268 2009/09/20 00:41 899 99.22657119 2009/09/20 00:56 928 99.24876246 2009/09/20 01:12 872 99.25364643 2009/09/20 01:26 902 99.2604542 CW001P03-SH-E: CW001P03-SH-E: 2009/09/19 23:56 904 32.10759256 2009/09/20 00:11 899 32.10688525 2009/09/20 00:26 901 32.10960911 2009/09/20 00:41 900 32.10492405 2009/09/20 00:56 927 32.10638105 2009/09/20 01:12 872 32.09597414 2009/09/20 01:26 902 32.09691138 Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average values in date range | Excel Worksheet Functions | |||
| Filling in a table with changing range values | Excel Discussion (Misc queries) | |||
| Average of multiple range with error values | Excel Worksheet Functions | |||
| select date range then find average of values in another cell | Excel Worksheet Functions | |||
| Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
Linear Mode
