data analysis

[arun karkare]

Hello experts;
I have a huge data, say 100000 values, each value between 0 to 255.
Values are in random order.
I first find the average of all values. by simply using the AVERAGE formula.
Next I find the values which are greater than average. Highlight them, using "Conditional formatting"
'Continuously' highlighted values are called sets.
Then I need to find 'number of values' in each set.
I also need to know 'average value' of each set.
Following example will clarify it further;
data:39,29,45,56,57,59,25,56,78,89,77,65,45,43,48,58,59,45,44,67...etc
overall average:54.2
more than avg:39,29,45,56,57,59,25,56,78,89,77,65,45,43,48,58,59,45,44,67......
number of values in sets:3, 5, 2, 1....
avg value in a set;57.3, 73, 58.5, 67.....
Questions:
what formula I should use to extract following information:
number of values in sets,
average values for the set.

Many Thanks in advance for ur support.
-arun karkare

 

[JoeMo]

Welcome to the forum!
The code below can be used to analyze your data, assumed to be in column A starting in A1 as shown in the example. C1 is the average of all data points. Starting in C2 and down is the sequence: Count of successive values that exceed the overall average (values in a set), followed by the average of the values in the set. A test on 10,000 random values between 0 and 255 was analyzed in 0.9 seconds on a relatively old computer/processor. The data in the test contained 2497 sets.

Excel Workbook
	A	B	C
1	3		127.0981
2	100		1
3	218		218
4	113		7
5	121		219.7143
6	96		1
7	63		184
8	98		3
9	85		188.3333
10	121		1
11	194		159
12	221		2
13	204		172.5
14	214		1
15	245		228
16	252		4
17	208		205.75
18	109		1
19	102		144
20	102		1
21	184		168
22	74		2
23	9		166
24	13		2
25	29		199.5
26	81		3
27	35		193.3333
28	179		3
29	234		199.6667
30	152		2
Sheet1

Sub DataAnalysis()
Dim lR As Long, V As Variant, Avg As Double, ct As Long, Savg As Double
Dim n As Long, S As Double, ctS As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
V = Range("A1:A" & lR).Value
Avg = WorksheetFunction.Average(Range("A1:A" & lR))
Application.ScreenUpdating = False
[C1] = Avg
n = 1
NextSet:
Do
       For i = n To UBound(V, 1)
              If V(i, 1) > Avg Then
                     ct = ct + 1
                     n = i + 1
                     S = S + V(i, 1)
              ElseIf ct = 0 Then
                     n = n + 1
                     GoTo NextSet
              Else
                     Exit Do
              End If
       Next i
Loop While n <= UBound(V, 1)
If ct > 0 Then
       ctS = ctS + 1
       lR = Range("C" & Rows.Count).End(xlUp).Row
       Cells(lR + 1, "C") = ct
       Cells(lR + 2, "C") = S / ct
       ct = 0
       S = 0
       If n < UBound(V, 1) Then GoTo NextSet
End If
MsgBox ctS & " sets analyzed"
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Welcome to the forum!
The code below can be used to analyze your data, assumed to be in column A starting in A1 as shown in the example. C1 is the average of all data points. Starting in C2 and down is the sequence: Count of successive values that exceed the overall average (values in a set), followed by the average of the values in the set. A test on 10,000 random values between 0 and 255 was analyzed in 0.9 seconds on a relatively old computer/processor. The data in the test contained 2497 sets.
Sheet1

	A	B	C
1	3		127.0981
2	100		1
3	218		218
4	113		7
5	121		219.7143
6	96		1
7	63		184
8	98		3
9	85		188.3333
10	121		1
11	194		159
12	221		2
13	204		172.5
14	214		1
15	245		228
16	252		4
17	208		205.75
18	109		1
19	102		144
20	102		1
21	184		168
22	74		2
23	9		166
24	13		2
25	29		199.5
26	81		3
27	35		193.3333
28	179		3
29	234		199.6667
30	152		2

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sub DataAnalysis()
Dim lR As Long, V As Variant, Avg As Double, ct As Long, Savg As Double
Dim n As Long, S As Double, ctS As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
V = Range("A1:A" & lR).Value
Avg = WorksheetFunction.Average(Range("A1:A" & lR))
Application.ScreenUpdating = False
[C1] = Avg
n = 1
NextSet:
Do
       For i = n To UBound(V, 1)
              If V(i, 1) > Avg Then
                     ct = ct + 1
                     n = i + 1
                     S = S + V(i, 1)
              ElseIf ct = 0 Then
                     n = n + 1
                     GoTo NextSet
              Else
                     Exit Do
              End If
       Next i
Loop While n <= UBound(V, 1)
If ct > 0 Then
       ctS = ctS + 1
       lR = Range("C" & Rows.Count).End(xlUp).Row
       Cells(lR + 1, "C") = ct
       Cells(lR + 2, "C") = S / ct
       ct = 0
       S = 0
       If n < UBound(V, 1) Then GoTo NextSet
End If
MsgBox ctS & " sets analyzed"
Application.ScreenUpdating = True
End Sub

Given that you have 100,000 cells to analyze, I made some modifications to reduce run time for the code I posted. This version places the overall average in C1, and starting in C2:D2 the number of data points in a set (col C) and the average value for the set (col D). A test of 100,000 random values which contained 24,995 sets ran in less than 0.2 seconds on a relatively slow processor.

Sub DataAnalysis()
Dim lR As Long, V As Variant, Avg As Double, ct As Long, Savg As Double
Dim n As Long, S As Double, ctS As Long, Vout As Variant
lR = Range("A" & Rows.Count).End(xlUp).Row
V = Range("A1:A" & lR).Value
ReDim Vout(1 To UBound(V, 1), 1 To 2)
Avg = WorksheetFunction.Average(Range("A1:A" & lR))
Application.ScreenUpdating = False
Columns("C:D").ClearContents
[C1] = Avg
n = 1
NextSet:
Do
       For i = n To UBound(V, 1)
              If V(i, 1) > Avg Then
                     ct = ct + 1
                     n = i + 1
                     S = S + V(i, 1)
              ElseIf ct = 0 Then
                     n = n + 1
                     GoTo NextSet
              Else
                     Exit Do
              End If
       Next i
Loop While n <= UBound(V, 1)
If ct > 0 Then
       ctS = ctS + 1
       Vout(ctS, 1) = ct
       Vout(ctS, 2) = S / ct
       ct = 0
       S = 0
       If n < UBound(V, 1) Then GoTo NextSet
End If
Range("C2:D" & ctS + 1).Value = Vout
MsgBox ctS & " sets analyzed"
Application.ScreenUpdating = True
End Sub

 

[arun karkare]

Hi Joe,
Many many Thanks for the support.
-arun