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

Hi,

That's a lot of words and if I've understood them correctly, try this

=SUM(INDIRECT("A2:A"&A1+1))


Mike

"Tom" wrote:

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

Hi,

Reading it again perhaps it's this you want

=SUM(INDIRECT("H" & ROW(A2) & ":H" & $A$1+ROW(A1)))

Mike

"Mike H" wrote:

Hi,

That's a lot of words and if I've understood them correctly, try this

=SUM(INDIRECT("A2:A"&A1+1))


Mike

"Tom" wrote:

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

Thanks for the help. I'm not too sure it's what I need.
I'm not familiar with INDIRECT but here's what I tried and got a syntax error.
=SUM("RawData!C"&"2":"RawData!C"&$Z$1+1)
RawData!C2 is the location of the START of the data to be SUM'd
$Z$1 = the number of rows I'd like to sum (for this example it's =4)
RawData!C$Z$1+1 should be = 5
In theory, once I copy the above formula (=SUM("RawData....), Excel should
continue to increment from Row 2 to Row 3... and from Row Z$1+1 to Row Z$1+2
etc.
So, for the first cell Excel would provide =SUM(RawData!C2:RawData!C5) then
=SUM(RawData!C3:RawData!C6) .... for $Z$1=4.
For $Z$1=10, I'd have =Sum(RawData!C2:RawData!C11) then
=SUM(RawData!C3:RawData!C12)...
Maybe I just need a boost on how to do that. Maybe my description wasn't
clear and clean enough.
Thanks!


"Mike H" wrote:

Hi,

Reading it again perhaps it's this you want

=SUM(INDIRECT("H" & ROW(A2) & ":H" & $A$1+ROW(A1)))

Mike

"Mike H" wrote:

Hi,

That's a lot of words and if I've understood them correctly, try this

=SUM(INDIRECT("A2:A"&A1+1))


Mike

"Tom" wrote:

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