Excel repatation of Formula
 

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!!!!!!

Excel repatation of Formula
 

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" wrote:

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!!!!!!

Excel repatation of Formula
 

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" wrote:

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" wrote:

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!!!!!!

Excel repatation of Formula
 

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,




"raj74" wrote:

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" wrote:

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" wrote:

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!!!!!!

Excel repatation of Formula
 

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$3:D3)*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.


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
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,




"raj74" wrote:

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" wrote:

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" wrote:

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!!!!!!

Excel repatation of Formula
 

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" wrote:

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$3:D3)*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.


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
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,




"raj74" wrote:

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" wrote:

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" wrote:

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!!!!!!

Excel repatation of Formula
 

Just include the sheet name where the data is located:

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


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...

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" wrote:

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$3:D3)*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.


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
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,




"raj74" wrote:

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" wrote:

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" wrote:

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!!!!!!

Excel repatation of Formula
 

Biff very sleek & fast. thanks.

"T. Valko" wrote:

Just include the sheet name where the data is located:

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


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...

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" wrote:

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$3:D3)*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.


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
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,




"raj74" wrote:

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" wrote:

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" wrote:

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!!!!!!

Excel repatation of Formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
Biff very sleek & fast. thanks.

"T. Valko" wrote:

Just include the sheet name where the data is located:

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


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...

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" wrote:

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$3:D3)*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.


--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
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,




"raj74" wrote:

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" wrote:

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" wrote:

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!!!!!!