Average per block

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PP - 30 Nov 2006 12:08 GMT

Hi all,

Could anyone help me in solving this problem.

I have a dataset in one column (G) containing only numbers. Every nth
row in that column is empty (so it varies). In this empty cell I want
to calculate the average of the n cells above. n is defined by the next
empty row. The data looks like this:
G
3
3
3
empty
3
3
empty
3
3
3
3
3
empty

My question is: Is there a VBA-sollution to insert an average in every
empty cell and where the average is based on the n cells above untill
its hits another empty cell.

Reply to this Message

Bob Phillips - 30 Nov 2006 12:58 GMT

Public Sub ProcessData()
Const TEST_COLUMN As String = "G" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim istart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
istart = 1
For i = 2 To iLastRow + 1
If .Cells(i, TEST_COLUMN).Value = "" Then
.Cells(i, TEST_COLUMN).Value =
Application.Average(.Cells(istart, TEST_COLUMN).Resize(i - istart + 1))
istart = i + 1
End If
Next i

End With

End Sub

Signature

HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi all,
>
[quoted text clipped - 22 lines]
> empty cell and where the average is based on the n cells above untill
> its hits another empty cell.

Reply to this Message

PP - 30 Nov 2006 14:18 GMT

Dear Bob,

Thanks very much for your reply. It works great. Since I am a newbe on
VBA I knew this was possible, just not on how to program it.

Still, I was wondering if it is possible to modify the code in such a
way that instead of calculating the average underneath the blocks, it
calculates it above the blocks. I've tried to alter some plus and minus
signs, but that didn't work.

Thanks,
Pim

Reply to this Message

Bob Phillips - 30 Nov 2006 17:24 GMT

Assuming that there is a blank at the top

Public Sub ProcessData()
Const TEST_COLUMN As String = "G" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
iStart = iLastRow
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value = "" Then
.Cells(i, TEST_COLUMN).Value = Application.Average _
(.Cells(i + 1, TEST_COLUMN).Resize(iStart - i))
iStart = i - 1
End If
Next i

End With

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

> Dear Bob,
>
[quoted text clipped - 8 lines]
> Thanks,
> Pim

Reply to this Message

PP - 30 Nov 2006 19:22 GMT

Dear Bob,

many thanx!! You safed me a ton load of boring work!

I first had to cut your commentline and paste on a new line underneath
the line. On the Windowsmachine I used this afternoon this wasn't
necessary, but on my Mac this was... .

Again many thanx!
Pim

Reply to this Message