Calculating average of a variable array

Posted by brian charles on October 18, 1999 8:36 AM

I would like to have a horizontal line drawn
on a scatter graph that represents the average
of all values.

I figured that I could do it if I create a second
series with all values equal to the average. I am
having trouble because the initial set of values that
is averaged, is variable. Thus, when creating the
formula: Average(RC[-1]:R["?"]C[-1]), I don't know what
to replace my "?" with.

Please Help.

thanks

Brian Charles

Posted by Chris on October 18, 1999 9:10 AM

Brian,

You should be able to set an average function to a range beyond the required range. Excel will not include blank cells into the calc.

For an explanation on how to include the average on the chart, see this post:

Re: Graphs - Chris 11:19:08 9/02/99 (2)

HTH,
Chris

Posted by Brian Charles on October 18, 1999 1:12 PM

My problem is that I have parsed the data, say 800
rows, into two sets separated by a blank row. My
important data is above the blank. One day it may
be 50 rows, another 10 rows, another 600. How do
I write the VBA code to calculate average over the
range (Activecell:Activecell.end(xldown). In
essence, the formula
Average(activecell:Activecell.end(xldown)). Needs
to be pasted into a collumn.

Posted by brian charles on October 19, 1999 5:55 AM

The following worked for me, as part of my Sub:

x = ActiveCell.Row

ActiveCell.Offset(1, 0).Formula = "=Average(R[" & -(x - 1) & "]C:R[-2]C)"

This calculated the average of all cells above a given cell in Row x,
except for Row 1, which is a header.