Excel 2003 SP3
I have a need to perform an arithmetic expression on a set of rows based
upon the value for the number of rows to be operated on in A1. For example,
I'd like to SUM() the numeric values in Column $H for all rows in groups of
four when "N=4" in A1. This is normally done via SUM($H2:$H5) which sums the
group of cell entries from H2 through and including H5. My task is to
develop a formula such that I can vary what's in A1 from 4 to 10 or whatever
so that I don't have to manually change the SUM() function each time I change
the A1 value.
For example, if I have "N=4" in A1 and it to change "N=10" then the SUM()
function would automatically change from SUM($H2:$H5) to SUM($H2:$H11) for
the first value to be displayed and then SUM($H3:$H6) to SUM($H3:$H12) etc on
down for approximately 2500+ rows.
The formula would look something like SUM($H(Row(I-(N+1):$HRow(I)), where
I=ROW() [current row #], N is the value in A1. The above produces an error.
I need a jump start on determining variable rows in a formula.
I would probably need something like IF(ROW()-Value(A1)<1,"",put calculation
here) so that when the generated value for current row minus the N= value is
<1 I just produce a blank (null) in the cell.
TIA