Excel repatation of Formula

[raj74]

I can not solve the following problem,

Let say we have a monthly discharge data of 20 years written in Col A1 to
A240.
Now I want to have the yearly data as a output in (C1 : C20) for 20 years,
where C1 will be sum of col A1:A12, C2 will be sum of colA13:A24 and so on.

I write the formula in C1 = sum(A1:A12). After when i drag the cell downward
the col C2 to C20 is not giving the value correctly, C2 only calculates the
sum of A2 to A13 ie A2:A13 (adding 1 to A1 and 1 to A12) and not the correct
sum of A13:A24. How to figure it out??

Thanks!!!!!!

 

[driller]

if i understand it, place the formula in C1 and drag down to C20.

=SUM(INDIRECT("a"&ROW()*12-11&":a"&ROW()*12))

not tested.

regards,

 

[raj74]

Thanks, I want it in a generalised way. Your solution is absolutely ok for
the case I have posted.
Actually i wanted to sumup say 1st 12 valus of col in a cell which may be
anywhere say C1 or D5 or in different worksheet together.just next col below
the 1st output will give up the sum up of next 12 values of the imput. 3
output cell gives the sum of next 12 values and so on.

If A4:A243 has total 240 values as an input, then I want the first output
to be written at any cell no, say F17= Sum(A4:A15), and next output cells
below the F17 ie F18, F19... will have the output of sum(A16:A27),
sum(A28:A39) ......respectively. I can write the equation for for each of 20
output cell. But without writing this, I can write the first and even can the
second output cell and drag the cell down to get the outher values. The
symmetry is that each output cell will calculate the sum of next fixed nuber
cells (here it is 12) of the previous output cell. Can we do that the way i
wanted, Thanks anyway for the help.

 

[driller]

very well, the reply suits your 1st clear absolute question.

it will be little complicated if you want a genalized solution...

1) make a range name for the whole set of 12's data.
I.E. A4:A243 ---i use a fixed range named as "scrub".

2) say on same worksheet, <excluding 1st row of the sheet>, type
on E1 (leave as blank)
on D2 = 1
on E2 = SUMPRODUCT(--scrub,--(ROW(scrub)<=12*D2+3))-SUM(E$1:E1)

3) select D2:E2 and drag down.

volatility not yet tested.

regards,

 

[T. Valko]

Try this:

You can enter this formula in *any* cell. Let's assume you enter the first
formula in cell D3.

=SUM(OFFSET(A$1,ROWS(D$33)*12-12,,12))

Copy down as needed.

D3 = sum of A1:A12
D4 = sum of A13:A24
D5 = sum of A25:A36
D6 = sum of A37:A48
etc
etc

In the formula, the ROWS(...) function needs to refer to the *first* cell
you enter the formula in.

 

[driller]

Biff, the offset function works fine. what will the formula look like if the
data and the formula is requested to be in different <separate> worksheet ?

regards,

 

[T. Valko]

Just include the sheet name where the data is located:

=SUM(OFFSET(Sales!A$1,ROWS(D$33)*12-12,,12))

 

[driller]

Biff very sleek & fast. thanks.

Just include the sheet name where the data is located:

=SUM(OFFSET(Sales!A$1,ROWS(D$33)*12-12,,12))

 

[T. Valko]

You're welcome. Thanks for the feedback!