Projection formula
 

Could anyone helpeith a formula
I have several tasks, each starts at a different time & take varying times
to complete
See table below
Task 1 starts in Jan, lasts for 4 months & I spend £1000 (ie £250/month)
Task 2 starts in Feb for 3 months & costs £3000 (£1000/month)

task start time value Jan Feb Mar Apr
1 Jan 4 £1,000 £250 £250 £250 £250
2 Feb 3 £3,000 £1,000 £1,000 £1,000

I need a formula that matches the start date in ColB with the month (Jan,
Feb etc), calculates the spend/month & fills in the table accordingly.
Dates could be spread ove 2 years

Any ideas

Projection formula
 

This doesn't take account of a period that runs over a year end

=IF(AND(MONTH(E$1)=MONTH($B2),MONTH(E$1)<=MONTH($ B2)+$C2-1),$D2/$C2,"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Saintsman" wrote in message
...
Could anyone helpeith a formula
I have several tasks, each starts at a different time & take varying times
to complete
See table below
Task 1 starts in Jan, lasts for 4 months & I spend £1000 (ie £250/month)
Task 2 starts in Feb for 3 months & costs £3000 (£1000/month)

task start time value Jan Feb Mar Apr
1 Jan 4 £1,000 £250 £250 £250 £250
2 Feb 3 £3,000 £1,000 £1,000 £1,000

I need a formula that matches the start date in ColB with the month (Jan,
Feb etc), calculates the spend/month & fills in the table accordingly.
Dates could be spread ove 2 years

Any ideas

Projection formula
 

Bob
Thanks for quick response
Am I missing something - when I get to the end of a period I want the
spending to stop, this formula doesn't do that

"Bob Phillips" wrote:

This doesn't take account of a period that runs over a year end

=IF(AND(MONTH(E$1)=MONTH($B2),MONTH(E$1)<=MONTH($ B2)+$C2-1),$D2/$C2,"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Saintsman" wrote in message
...
Could anyone helpeith a formula
I have several tasks, each starts at a different time & take varying times
to complete
See table below
Task 1 starts in Jan, lasts for 4 months & I spend £1000 (ie £250/month)
Task 2 starts in Feb for 3 months & costs £3000 (£1000/month)

task start time value Jan Feb Mar Apr
1 Jan 4 £1,000 £250 £250 £250 £250
2 Feb 3 £3,000 £1,000 £1,000 £1,000

I need a formula that matches the start date in ColB with the month (Jan,
Feb etc), calculates the spend/month & fills in the table accordingly.
Dates could be spread ove 2 years

Any ideas

Projection formula
 

Bob
Deepest apologies - it does work!
Many thanks

"Bob Phillips" wrote:

This doesn't take account of a period that runs over a year end

=IF(AND(MONTH(E$1)=MONTH($B2),MONTH(E$1)<=MONTH($ B2)+$C2-1),$D2/$C2,"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Saintsman" wrote in message
...
Could anyone helpeith a formula
I have several tasks, each starts at a different time & take varying times
to complete
See table below
Task 1 starts in Jan, lasts for 4 months & I spend £1000 (ie £250/month)
Task 2 starts in Feb for 3 months & costs £3000 (£1000/month)

task start time value Jan Feb Mar Apr
1 Jan 4 £1,000 £250 £250 £250 £250
2 Feb 3 £3,000 £1,000 £1,000 £1,000

I need a formula that matches the start date in ColB with the month (Jan,
Feb etc), calculates the spend/month & fills in the table accordingly.
Dates could be spread ove 2 years

Any ideas

Projection formula
 

Hi

Modifying Bob's formula to
=IF(AND(MONTH(E$1)+12*(YEAR(E$1)-YEAR($B$2))=MONTH($B2),(MONTH(E$1)+12*(YEAR(E$1)-YEAR($B$2)))<=MONTH($B2)+$C2-1),$D2/$C2,"")

will deal with projects continuing beyond 12 months.

--
Regards

Roger Govier


"Saintsman" wrote in message
...
Bob
Deepest apologies - it does work!
Many thanks

"Bob Phillips" wrote:

This doesn't take account of a period that runs over a year end

=IF(AND(MONTH(E$1)=MONTH($B2),MONTH(E$1)<=MONTH($ B2)+$C2-1),$D2/$C2,"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Saintsman" wrote in message
...
Could anyone helpeith a formula
I have several tasks, each starts at a different time & take
varying times
to complete
See table below
Task 1 starts in Jan, lasts for 4 months & I spend £1000 (ie
£250/month)
Task 2 starts in Feb for 3 months & costs £3000 (£1000/month)

task start time value Jan Feb Mar
Apr
1 Jan 4 £1,000 £250 £250 £250 £250
2 Feb 3 £3,000 £1,000 £1,000
£1,000

I need a formula that matches the start date in ColB with the month
(Jan,
Feb etc), calculates the spend/month & fills in the table
accordingly.
Dates could be spread ove 2 years

Any ideas

Projection formula
 

Thanks very much


"Roger Govier" wrote:

Hi

Modifying Bob's formula to
=IF(AND(MONTH(E$1)+12*(YEAR(E$1)-YEAR($B$2))=MONTH($B2),(MONTH(E$1)+12*(YEAR(E$1)-YEAR($B$2)))<=MONTH($B2)+$C2-1),$D2/$C2,"")

will deal with projects continuing beyond 12 months.

--
Regards

Roger Govier


"Saintsman" wrote in message
...
Bob
Deepest apologies - it does work!
Many thanks

"Bob Phillips" wrote:

This doesn't take account of a period that runs over a year end

=IF(AND(MONTH(E$1)=MONTH($B2),MONTH(E$1)<=MONTH($ B2)+$C2-1),$D2/$C2,"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Saintsman" wrote in message
...
Could anyone helpeith a formula
I have several tasks, each starts at a different time & take
varying times
to complete
See table below
Task 1 starts in Jan, lasts for 4 months & I spend £1000 (ie
£250/month)
Task 2 starts in Feb for 3 months & costs £3000 (£1000/month)

task start time value Jan Feb Mar
Apr
1 Jan 4 £1,000 £250 £250 £250 £250
2 Feb 3 £3,000 £1,000 £1,000
£1,000

I need a formula that matches the start date in ColB with the month
(Jan,
Feb etc), calculates the spend/month & fills in the table
accordingly.
Dates could be spread ove 2 years

Any ideas