Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rota Projection | Excel Discussion (Misc queries) | |||
forecast or projection | Excel Worksheet Functions | |||
Month End Projection formula? | Excel Worksheet Functions | |||
Projection of values | Excel Worksheet Functions | |||
dealing with a projection | Charts and Charting in Excel |