Ken, that macro works fabulously! It throws some zeros in empty cells but
that's easy to rectify. It does the whole sheet in one click on the mouse!
Thank you! You are fabulous. The fact that you even understood my jumble is
something else, and then to come up with the answer!! Wow! I'm impressed!
Thank you so much! Connie


"Ken Hudson" wrote:

Hi Connie,
You might try the following macro:

Option Explicit
Dim Anchor As Double
Dim Iloop As Double
Dim Rowcount As Double
Sub Compute_Avg()

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Rowcount = Cells(Rows.Count, "D").End(xlUp).Row
Anchor = 1
For Iloop = 2 To Rowcount
If IsEmpty(Cells(Iloop, "D")) Then
Cells(Iloop, "D") = "=Average(D" & Anchor & ":D" & Iloop - 1 & ")"
Anchor = Iloop + 1
End If
Next Iloop
Cells(Rowcount + 1, "D") = "=Average(D" & Anchor & ":D" & Iloop - 1 & ")"

'Turn on warnings, etc.
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

--
Ken Hudson


"Connie Martin" wrote:

I recorded the macro below which needs a little editing and I don't know how
to do it. What I want it to do is average the numbers above the cell
selected starting with the first number below the first blank cell above the
cell selected. That sounds complicated! For example, the cell selected is
D107. D102 is a blank cell. There are numbers in 103 to 106. I want the
macro to run so that it will average the numbers in those cells above. If I
place the cursor in D101 and there are numbers in D77 to D100, then I want it
to average those numbers. I've added an additional step in the macro after
that, but that part of the macro is running fine. It's just that when I
recorded the macro I selected D77 to D100 to average it in D101, and now each
time I run the macro it averages the same number of cells as when I recorded
the macro. If no one answers this, I don't blame you because I am probably
not explaining it very well. Here's the macro, if you can make any heads or
tails about it!! I believe it's just the first line in the macro that needs
editing. Connie

Sub AVERAGE()
'
' AVERAGE Macro
' Macro recorded 5/14/2008 by Connie Martin
'

'
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-24]C:R[-1]C)"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-4]/30"
ActiveCell.Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub