help with a formula please
 

Hello,

I have a long list of numbers (over 600) in a single column, say they are in
A1..A600 (they are daily meter readings) .

In column B I want to subtotal them in groups of 30, or 90, or 365 , or 7,
where the number of days being subtotalled is specified in another cell ?

So in B600 I would have =SUM(A571:A600) for 30 days, or =SUM(A511:A600) for
90 days etc. , where the starting cell A571 or A511 is determined by a
number, ie 30 or 90, in cell C600

How do I arrange for the first cell in the SUM( ) formula to be
determined in this way?

Can someone point me in the right direction please

Thanks as always

K

help with a formula please
 

K,

With the number of days (30, 90, etc.) in cell B2:

=SUM(OFFSET(A600,-(B2-1),0,B2,1))

HTH,
Bernie
MS Excel MVP


"KRK" wrote in message
...
Hello,

I have a long list of numbers (over 600) in a single column, say they are in A1..A600 (they are
daily meter readings) .

In column B I want to subtotal them in groups of 30, or 90, or 365 , or 7, where the number of
days being subtotalled is specified in another cell ?

So in B600 I would have =SUM(A571:A600) for 30 days, or =SUM(A511:A600) for 90 days etc. , where
the starting cell A571 or A511 is determined by a number, ie 30 or 90, in cell C600

How do I arrange for the first cell in the SUM( ) formula to be determined in this way?

Can someone point me in the right direction please

Thanks as always

K

help with a formula please
 

Hi

There are a variety of ways that you could this:


=SUM((INDIRECT("A"&(600-C600+1)):A600))

This presumes the last line of your data is in A600 - the period that you
want to examine is in C600.
Change the cell references as required

edvwvw




KRK wrote:
Hello,

I have a long list of numbers (over 600) in a single column, say they are in
A1..A600 (they are daily meter readings) .

In column B I want to subtotal them in groups of 30, or 90, or 365 , or 7,
where the number of days being subtotalled is specified in another cell ?

So in B600 I would have =SUM(A571:A600) for 30 days, or =SUM(A511:A600) for
90 days etc. , where the starting cell A571 or A511 is determined by a
number, ie 30 or 90, in cell C600

How do I arrange for the first cell in the SUM( ) formula to be
determined in this way?

Can someone point me in the right direction please

Thanks as always

K

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200910/1

help with a formula please - Thanks
 

Hello again & thanks for the help.

KK



"KRK" wrote in message
...
Hello,

I have a long list of numbers (over 600) in a single column, say they are
in A1..A600 (they are daily meter readings) .

In column B I want to subtotal them in groups of 30, or 90, or 365 , or
7, where the number of days being subtotalled is specified in another cell
?

So in B600 I would have =SUM(A571:A600) for 30 days, or =SUM(A511:A600)
for 90 days etc. , where the starting cell A571 or A511 is determined by a
number, ie 30 or 90, in cell C600

How do I arrange for the first cell in the SUM( ) formula to be
determined in this way?

Can someone point me in the right direction please

Thanks as always

K