Help with averages!

[sarah_l92]

Hello, I have a list of numbers in the one column like so:

9.26
9.24
9.22
9.21
9.25
9.29
9.26
9.24
9.32
9.28
9.3
9.27
9.24
9.28
9.25
9.29
9.22
9.28
9.26
9.24
9.22
9.26
9.25
9.18
9.29
9.3
9.23
9.26
9.25
9.21
9.15
9.22
9.3
9.28
9.24
9.26
9.26
9.23
9.3
9.29
9.31
9.23
9.25
9.31
8.91
9.89
9.92
9.96
9.9
9.85
9.79
9.79
9.89
9.92
9.87
9.8
9.83
9.89
9.84
9.83
9.85
9.87
9.85
9.85
9.89
9.91
9.88
9.86
9.87
9.91
9.91
9.87
9.86
9.8
9.84
9.89
9.93
9.87
9.87
9.89
9.87
9.82
9.88
9.91
9.91
9.88
9.88
9.87
9.86
9.89
11.6
11.53
11.61
11.51
11.59
11.58
11.55
11.56
11.54
11.57
11.56
11.55
11.56
11.56
11.59
11.57
11.52
11.55
11.58
11.56
11.58
11.48
11.52
11.59
11.61
11.51
11.57
11.52
11.5
11.55
11.52
11.48
11.53
11.51
11.52
11.5
11.53
11.56
11.59
11.49
11.46
11.56
11.53
11.52
11.57
11.6
11.53
9.12
9.09
9.11
9.14
9.17
9.15
9.14
9.14
9.15
9.16
9.16
9.14
9.17
9.15
9.14
9.14
9.14
9.12
9.11
9.12
9.16
9.12
9.1
9.12
9.13
9.13
9.11
9.14
9.16
9.14
9.12
9.14
9.14
9.12
9.09
9.11
9.13
9.13
9.12
9.13
9.13
9.1
9.13
9.16
9.13
9.13
9.15
9.07
10.42
10.41
10.42
10.36
10.38
10.42
10.38
10.4
10.41
10.44
10.4
10.38
10.37
10.38
10.39
10.38
10.41
10.46
10.45
10.36
10.45
10.42
10.39
10.47
10.44
10.45
10.41
10.4
10.43
10.39
10.39
10.42
10.41
10.42
10.44
10.42
10.45
10.44
10.47
10.39
10.35
10.42
10.44
10.42
10.39
11.99
11.87
11.87
11.9
11.85
11.88
11.82
11.81
11.87
11.78
11.82
11.91
11.88
11.83
11.82
11.89
11.87
11.85
11.86
11.85
11.79
11.85
11.84
11.77
11.86
11.78
11.73
11.74
11.82
11.81
11.77
11.86
11.84
11.81
11.77
11.8
11.8
11.79
11.82
11.78
11.76
11.74
11.74
11.47
8.23
8.27
8.18
8.14
8.25
8.2
8.27
8.34
8.27
8.27
8.32
8.23
8.27
8.24
8.24
8.28
8.25
8.21
8.28
8.21
8.23
8.19
8.26
8.13
8.12
8.21
8.24
8.27
8.3
8.31
8.22
8.15
8.14
8.11
8.24
8.18
8.25
8.3
8.31
8.3
8.28
8.25
8.22
8.26
8.26
8.17
10.49
10.47
10.43
10.41
10.42
10.47
10.52
10.47
10.48
10.46
10.47
10.47
10.51
10.42
10.4
10.43
10.47
10.46
10.47
10.47
10.48
10.44
10.45
10.46
10.44
10.42
10.43
10.46
10.47
10.45
10.45
10.38
10.45
10.46
10.44
10.39
10.38
10.44
10.45
10.48
10.46
10.46
10.44
10.44
10.44
10.45
10.42
8.66
8.65
8.62
8.65
8.65
8.61
8.58
8.59
8.61
8.56
8.63
8.6
8.58
8.64
8.58
8.62
8.61
8.58
8.61
8.59
8.58
8.57
8.62
8.62
8.63
8.61
8.57
8.63
8.65
8.65
8.66
8.65
8.59
8.64
8.66
8.61
8.64
8.66
8.63
8.6
8.59
8.65
8.61
8.62
8.6
8.56
8.59
8.65
8.63
8.58
8.59
8.6
8.59
8.58
8.59
8.59
8.56
8.54
8.61
8.48
9.55
9.51
9.49
9.51
9.49
9.49
9.53
9.48
9.45
9.51
9.53
9.54
9.51
9.48
9.45
9.47
9.43
9.47
9.46
9.52
9.47
9.39
9.42
9.51
9.57
9.55
9.44
9.49
9.46
9.44
9.5
9.5
9.52
9.47
9.45
9.51
9.47
9.42
9.53
9.5
9.48
9.5
9.43
9.47
9.44
9.45
9.48
9.54
9.57
9.49
9.48
9.82
9.9
9.76
9.77
9.97
10.08
9.97
9.86
9.98
9.94
10.04
9.88
9.76
9.96
9.93
10.01
10.01
9.82
9.95
9.98
9.98
9.95
9.93
9.92
9.93
10.01
10.04
10.01
9.91
10.02
10.09
9.91
9.96
10
10.03
10.03
10.07
9.97
9.88
9.9
9.9
9.94
9.92
10.06
9.89
9.77
9.84
9.79
9.77
9.97
9.94
9.87
10.01

<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

how can I find the average of each of the set of numbers when theres a gap? Is there a formula i can enter that will calculate the average of the first set of numbers before the blank cells and then a second average will be calculated from the first set of blank cells to the second and so forth?

thanks for any help x

 

[pjmorris]

assuming your first data element is in cell A2 then:

in cell B2 enter: =IF(A2 <> "",IF(B1="",0,B1)+A2,IF(C1 <> 0,B1/C1,""))
in cell C2 enter: =IF(A2<>"",C1+1,0)

Then copy those 2 cells down the list of data. At the first cell to the right of the blank in you column will be the that segments average.

Hope this makes sense.

Peter