Macro with "relative" range for Average function, or... 'is there a real keystroke recording macro g

  • Thread starter Thread starter jss
  • Start date Start date
J

jss

I often have columns of numbers, with a label, that I need to average (or
get the standard deviation, etc.). The column length varies, and I would
like to not worry about having a constant number of rows between the cell I
am in when I run the macro and the cell with the label.

Manually, I use

=average(
end, down arrow ' gets me to the label
down arrow ' first data cell
control shift down ' last data cell
Enter ' completes entry, calculates average

This works fine for other columns of the same length, but with other data it
will use the same sized range.

I tried a few phrases in Google, but didn't see anything other than the
recommendation to use the relative reference setting. This does work better
than absolute, but the important part of the macro turns out the same in
either case:


ActiveCell.FormulaR1C1 = "=AVERAGE(R[11]C:R[238]C)"

Is there a way to do this or to actually record keystrokes in Excel?

Thanks for any tips.
 
Hi,

Do you know where the label will be? Is it the first entry in the column?
Is the data solid, no blanks? Do you want the formula in the first blank
cell below the data or on a specific row? What column is your data in? Is
there data below the range you want to average that should not be counted?

One method would be:

Sub myAverage()
Dim top As Long
Dim bottom As Long
top = [D1].End(xlDown).Offset(1, 0).Row
bottom = [D65536].End(xlUp).Row
[D65536].End(xlUp).Offset(1, 0) = "=AVERAGE(D" & top & ":D" & bottom & ")"
End Sub
 
Most of the time the data is solid, the label is the first entry in the
column, and there is no data below the group being averaged and the method
you gave should work. I'll give it a try.

I'll also look for a faithful keystroke recorder

Thanks,

John

Shane Devenshire said:
Hi,

Do you know where the label will be? Is it the first entry in the column?
Is the data solid, no blanks? Do you want the formula in the first blank
cell below the data or on a specific row? What column is your data in?
Is
there data below the range you want to average that should not be counted?

One method would be:

Sub myAverage()
Dim top As Long
Dim bottom As Long
top = [D1].End(xlDown).Offset(1, 0).Row
bottom = [D65536].End(xlUp).Row
[D65536].End(xlUp).Offset(1, 0) = "=AVERAGE(D" & top & ":D" & bottom &
")"
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


jss said:
I often have columns of numbers, with a label, that I need to average (or
get the standard deviation, etc.). The column length varies, and I would
like to not worry about having a constant number of rows between the cell
I
am in when I run the macro and the cell with the label.

Manually, I use

=average(
end, down arrow ' gets me to the label
down arrow ' first data cell
control shift down ' last data cell
Enter ' completes entry, calculates average

This works fine for other columns of the same length, but with other data
it
will use the same sized range.

I tried a few phrases in Google, but didn't see anything other than the
recommendation to use the relative reference setting. This does work
better
than absolute, but the important part of the macro turns out the same in
either case:


ActiveCell.FormulaR1C1 = "=AVERAGE(R[11]C:R[238]C)"

Is there a way to do this or to actually record keystrokes in Excel?

Thanks for any tips.
Click to expand...
Click to expand...
 
Back
Top