RE: Variable range column summation and averaging by Tom
Tom
Fri Mar 14 23:10:00 CDT 2008
Sorry I didn't make things clearer.
I think what I need is the ability to have the 'n' (# of summation cells)
determine the start and end of the ranges. That is, given that Column F
contains the data to be summed and with n=4 then I'd need to start at $F2 and
end at $F5. This would be summed via SUM($F2:$F5) which yields 4 cells ($F2,
$F3, $F4 and $F5).
So, if I wanted n=6, I would then start at $F1 and end at $F7 (includes the
6 cells of $F2, $F3, $F4, $F5, $F6 and $F7). Using a formula or whatever, if
n=i, how do I setup $F2 thru $F(i+1+n)? Keep in mind that the next summation
would start at $F3 and go through $F(i+1+n+1) and on down the column. [I'm
putting the SUM()/n in a different column (like a Column G) that Column F.
For n=4 I would need to do an IF statement for Column F, Rows 2, 3 and 4 to
force the SUM() calculation to NOT occur since $F1 holds the Header Row, $F2
is the start of the data but would not be summed for averages of n=4; $F3 is
the next cell to not be included; $F4 is the next cell after $F3 to not be
included and $F5 would be the last cell to be included for the SUM()/4
calculations.
For example for n=4
Column F Column G
Row 1 Header F Header G
Row 2 1.5 Stays blank if n=4
Row 3 2.5 Stays blank if n=4
Row 4 3.5 Stays blank if n=4
Row 5 4.5 SUM($F2:$F4)/4=(1.5+2.5+3.5+4.5)/4=12/4=3
Row 6 5.5 SUM($F3:$F5)/4=(2.5+3.5+4.5+5.5)/4=16/4=4
etc.....
Hopefully you follow me here!
Thanks for your response!
Tom
"pdberger" wrote:
> Tom --
>
> Not exactly sure what the data scheme looks like, but it seems that where
> you need an 'n', you can use either =COUNT() or =COUNTA(), no?
>
> HTH
>
> "Tom" wrote:
>
> > Excel 2003 SP3
> > WinXP Pro SP2
> > Header in Row 1
> > Data starts in Row 2
> >
> > I'd like to sum [SUM($Fx:$Fy)] a range of cells, n, in a column and obtain
> > their averages [SUM($Fx:$Fy)/n].
> >
> > My issue is that I'd like to vary the 'n' (cell range) by column so as to
> > compare the fitness of any particular 'n' to another relative to my data and
> > datapoints.
> >
> > So, I've manually setup 'n=4' and 'n=20' and 'n=21'. For n=20, each time I
> > need to manually count the starting point for the summation because the
> > previous 20 cells are blank. Then I need to change each column's 'x and y'
> > [start and end rows] to reflect the new range.
> >
> > How can I setup the formula for the averages so I can just reference a value
> > in, say, Row 1 or 2? Hence, I could have Row 2, Column F be '4' which would
> > then propagate the general formula to start displaying at $F6 for the
> > averages of SUM($F3:$F6)/4? The formula would need to know to start at Row 3
> > and continue adding through Row 6 and then divide by 4. (Note: Row '6' less
> > Row '3' = 3 then add 1 and I get the value of 4 for the range, if you
> > understand what I'm trying to say here.) I need the logic/formula for the
> > IF(...) and the establish how to do the ROW starting and ENDING points
> > relative to the quantity of datapoints to average.
> >
> > Maybe I'm not too sure how to test what ROW I'm on.....
> > THANKS!!!
> > Tom