Can anyone advise on te following:
Whe
Date A is input date
Month 1 (Jan) = x
Month 2 (Feb) = y
Month 3 (March) = z
& so on

Require formula to calculate as follows:
If date A = Jan then calculate x
If date A = Feb then calculate x+y
If date A = March then calculate x+y+z
and so on, required for a 24 month period
TTT

Try =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1))
assuming that your input date is in A1, and your month 1 data value is in
B2 (and that month 1 applies to Jan 2007, so that your 24 month period
covers Jan 2007 to Dec 2008).
Adjust as necessary.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
Date A is input date
Month 1 (Jan) = x
Month 2 (Feb) = y
Month 3 (March) = z
& so on

Require formula to calculate as follows:
If date A = Jan then calculate x
If date A = Feb then calculate x+y
If date A = March then calculate x+y+z
and so on, required for a 24 month period
TTT

David

Tried this but has come up with a formula error, seems there is a problem
with the last statement ),1)); any ideas what the problem could be; also if I
change the date it does not pick up the accumaltive totals.
--
TTT


"David Biddulph" wrote:

Try =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1))
assuming that your input date is in A1, and your month 1 data value is in
B2 (and that month 1 applies to Jan 2007, so that your 24 month period
covers Jan 2007 to Dec 2008).
Adjust as necessary.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
Date A is input date
Month 1 (Jan) = x
Month 2 (Feb) = y
Month 3 (March) = z
& so on

Require formula to calculate as follows:
If date A = Jan then calculate x
If date A = Feb then calculate x+y
If date A = March then calculate x+y+z
and so on, required for a 24 month period
TTT

No idea. It works fine for me.
Did you copy the formula into the formula bar, or did you retype it? Did
you make any changes? Copy the formula from the formula bar back here and
we'll have a look at it.
--
David Biddulph

"tomttom40" wrote in message
...
David

Tried this but has come up with a formula error, seems there is a problem
with the last statement ),1)); any ideas what the problem could be; also
if I
change the date it does not pick up the accumaltive totals.
--
TTT


"David Biddulph" wrote:

Try =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1))
assuming that your input date is in A1, and your month 1 data value is
in
B2 (and that month 1 applies to Jan 2007, so that your 24 month period
covers Jan 2007 to Dec 2008).
Adjust as necessary.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
Date A is input date
Month 1 (Jan) = x
Month 2 (Feb) = y
Month 3 (March) = z
& so on

Require formula to calculate as follows:
If date A = Jan then calculate x
If date A = Feb then calculate x+y
If date A = March then calculate x+y+z
and so on, required for a 24 month period
TTT

David

Formula is now accepted (must have been a typing error); thank-you

Still have a problem;

As follows:
Col1 Col2 Col3
Jan Feb March
£15 £15 £15

If I input 1/1/07 into a seperate cell (A1 from your formula) the finished
sum should equal £15 (B2 from your formula)
If I input 1/2/07 into a the A1 cell the calculation should equal £30
If I inut 1/3/07 into A1 the calculation should equla £45
and so on

Any ideas???
--
TTT


"David Biddulph" wrote:

No idea. It works fine for me.
Did you copy the formula into the formula bar, or did you retype it? Did
you make any changes? Copy the formula from the formula bar back here and
we'll have a look at it.
--
David Biddulph

"tomttom40" wrote in message
...
David

Tried this but has come up with a formula error, seems there is a problem
with the last statement ),1)); any ideas what the problem could be; also
if I
change the date it does not pick up the accumaltive totals.
--
TTT


"David Biddulph" wrote:

Try =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1))
assuming that your input date is in A1, and your month 1 data value is
in
B2 (and that month 1 applies to Jan 2007, so that your 24 month period
covers Jan 2007 to Dec 2008).
Adjust as necessary.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
Date A is input date
Month 1 (Jan) = x
Month 2 (Feb) = y
Month 3 (March) = z
& so on

Require formula to calculate as follows:
If date A = Jan then calculate x
If date A = Feb then calculate x+y
If date A = March then calculate x+y+z
and so on, required for a 24 month period
TTT

You presumably didn't look in Excel help to see the syntax of the OFFSET
function?
If you've got the data in a row, rather than in a column, then change the
formula from =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1)) to
=SUM(OFFSET(B2,0,0,1,12*(YEAR(A1)-2007)+MONTH(A1)))
--
David Biddulph

"tomttom40" wrote in message
...
David

Formula is now accepted (must have been a typing error); thank-you

Still have a problem;

As follows:
Col1 Col2 Col3
Jan Feb March
£15 £15 £15

If I input 1/1/07 into a seperate cell (A1 from your formula) the finished
sum should equal £15 (B2 from your formula)
If I input 1/2/07 into a the A1 cell the calculation should equal £30
If I inut 1/3/07 into A1 the calculation should equla £45
and so on

Any ideas???
--
TTT


"David Biddulph" wrote:

No idea. It works fine for me.
Did you copy the formula into the formula bar, or did you retype it? Did
you make any changes? Copy the formula from the formula bar back here
and
we'll have a look at it.
--
David Biddulph

"tomttom40" wrote in message
...
David

Tried this but has come up with a formula error, seems there is a
problem
with the last statement ),1)); any ideas what the problem could be;
also
if I
change the date it does not pick up the accumaltive totals.
--
TTT


"David Biddulph" wrote:

Try =SUM(OFFSET(B2,0,0,12*(YEAR(A1)-2007)+MONTH(A1),1))
assuming that your input date is in A1, and your month 1 data value
is
in
B2 (and that month 1 applies to Jan 2007, so that your 24 month period
covers Jan 2007 to Dec 2008).
Adjust as necessary.
--
David Biddulph

"tomttom40" wrote in message
...
Can anyone advise on te following:
Whe
Date A is input date
Month 1 (Jan) = x
Month 2 (Feb) = y
Month 3 (March) = z
& so on

Require formula to calculate as follows:
If date A = Jan then calculate x
If date A = Feb then calculate x+y
If date A = March then calculate x+y+z
and so on, required for a 24 month period
TTT