Variable range column summation and averaging

[Tom]

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

 

[pdberger]

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]

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

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

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