|
Budget
I have a total amount of $52,000 and I want to show an equal amount in twelve
columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
In the September 2005 Column type =52000/12 then copy the cell across the
columns. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September
column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Sorry for the breifness, let me expand: 52000 is a total payment column. I
want the divided 12 months to automatically put in the correct columns across three years. Basically I have the total amount and want to schedule it throughout the years. EXAMPLE: A B C D E F .... Total Jan Feb Mar Apr May .... 52000 --- ---- (place divided amount begining in Mar'05 through '06) Thanks, JOHN "Ian" wrote: Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Hi John
If your entries in B1:AK1 were set up as 01/01/05, 01/02/05 etc. but formatted Format CellsNumberCustom mmm-yy Then in B2 =A2/36 in C2 =($A$2-SUM($B$2:B2))/(37-(MONTH(C1)+12*(YEAR(C1)-YEAR($B$1)))) and copy across through D2:AK2 Obviously this does not tell the system where to start entering the values, but deleting the formula in cells B2 and C2 would cause the 52000 to be spread over the remaining 34 months at 1529.41 per month. Regards Roger Govier John wrote: Sorry for the breifness, let me expand: 52000 is a total payment column. I want the divided 12 months to automatically put in the correct columns across three years. Basically I have the total amount and want to schedule it throughout the years. EXAMPLE: A B C D E F .... Total Jan Feb Mar Apr May .... 52000 --- ---- (place divided amount begining in Mar'05 through '06) Thanks, JOHN "Ian" wrote: Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Thanks Roger. However, since I need the start and ending here's what I have
done. A B C D E F G H I ETC... 50000 12MONTHS (START DATE) (END DATE) JAN FEB MAR APRIL MAY ETC... I need to take A1/B1 and put them in the correct 12 start and end columns using C1 and D1 dates. Some C1 dates start on Jan and some may start in May. Please help!! JOHN "Roger Govier" wrote: Hi John If your entries in B1:AK1 were set up as 01/01/05, 01/02/05 etc. but formatted Format CellsNumberCustom mmm-yy Then in B2 =A2/36 in C2 =($A$2-SUM($B$2:B2))/(37-(MONTH(C1)+12*(YEAR(C1)-YEAR($B$1)))) and copy across through D2:AK2 Obviously this does not tell the system where to start entering the values, but deleting the formula in cells B2 and C2 would cause the 52000 to be spread over the remaining 34 months at 1529.41 per month. Regards Roger Govier John wrote: Sorry for the breifness, let me expand: 52000 is a total payment column. I want the divided 12 months to automatically put in the correct columns across three years. Basically I have the total amount and want to schedule it throughout the years. EXAMPLE: A B C D E F .... Total Jan Feb Mar Apr May .... 52000 --- ---- (place divided amount begining in Mar'05 through '06) Thanks, JOHN "Ian" wrote: Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Hi John
There was an almost similar question posed by "Struggling of Essex" in this group. Are you the same person? Anyway, basically your answer should be the same as the one I posted to him. I assume row 1 has headings, and all data will be entered in row 2 onward. Columns E onward, enter in the format Jan-05, Feb-05 etc. Using the fill handle will increment these for you automatically. Enter your Start Date in the format Apr-05, Jul-05 etc. In column B, duration just enter 12 (or number of months for that project) Don't enter a value in column D, enter this formula =DATE(YEAR(C2),MONTH(C2)+B2-1,DAY(C2)) In cell E2 enter the following =IF(AND(E$1=$C2,E$1<=$D2),$A2/$B2,"") Copy across for the maximum length of your project (I think it was 36 months for you) i.e. through F2:AO2 Copy E2:AO2 Paste through E3:E50 or as many projects as you wish to run. Regards Roger Govier John wrote: Thanks Roger. However, since I need the start and ending here's what I have done. A B C D E F G H I ETC... 50000 12MONTHS (START DATE) (END DATE) JAN FEB MAR APRIL MAY ETC... I need to take A1/B1 and put them in the correct 12 start and end columns using C1 and D1 dates. Some C1 dates start on Jan and some may start in May. Please help!! JOHN "Roger Govier" wrote: Hi John If your entries in B1:AK1 were set up as 01/01/05, 01/02/05 etc. but formatted Format CellsNumberCustom mmm-yy Then in B2 =A2/36 in C2 =($A$2-SUM($B$2:B2))/(37-(MONTH(C1)+12*(YEAR(C1)-YEAR($B$1)))) and copy across through D2:AK2 Obviously this does not tell the system where to start entering the values, but deleting the formula in cells B2 and C2 would cause the 52000 to be spread over the remaining 34 months at 1529.41 per month. Regards Roger Govier John wrote: Sorry for the breifness, let me expand: 52000 is a total payment column. I want the divided 12 months to automatically put in the correct columns across three years. Basically I have the total amount and want to schedule it throughout the years. EXAMPLE: A B C D E F .... Total Jan Feb Mar Apr May .... 52000 --- ---- (place divided amount begining in Mar'05 through '06) Thanks, JOHN "Ian" wrote: Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Roger, No, to Struggling of Essex. This is my first time using this system.
Thank you for your patience and knowledge. i will attempt to use your formula tomorrow. UDAMAN Roger. Keep up the good work. JOHN "Roger Govier" wrote: Hi John There was an almost similar question posed by "Struggling of Essex" in this group. Are you the same person? Anyway, basically your answer should be the same as the one I posted to him. I assume row 1 has headings, and all data will be entered in row 2 onward. Columns E onward, enter in the format Jan-05, Feb-05 etc. Using the fill handle will increment these for you automatically. Enter your Start Date in the format Apr-05, Jul-05 etc. In column B, duration just enter 12 (or number of months for that project) Don't enter a value in column D, enter this formula =DATE(YEAR(C2),MONTH(C2)+B2-1,DAY(C2)) In cell E2 enter the following =IF(AND(E$1=$C2,E$1<=$D2),$A2/$B2,"") Copy across for the maximum length of your project (I think it was 36 months for you) i.e. through F2:AO2 Copy E2:AO2 Paste through E3:E50 or as many projects as you wish to run. Regards Roger Govier John wrote: Thanks Roger. However, since I need the start and ending here's what I have done. A B C D E F G H I ETC... 50000 12MONTHS (START DATE) (END DATE) JAN FEB MAR APRIL MAY ETC... I need to take A1/B1 and put them in the correct 12 start and end columns using C1 and D1 dates. Some C1 dates start on Jan and some may start in May. Please help!! JOHN "Roger Govier" wrote: Hi John If your entries in B1:AK1 were set up as 01/01/05, 01/02/05 etc. but formatted Format CellsNumberCustom mmm-yy Then in B2 =A2/36 in C2 =($A$2-SUM($B$2:B2))/(37-(MONTH(C1)+12*(YEAR(C1)-YEAR($B$1)))) and copy across through D2:AK2 Obviously this does not tell the system where to start entering the values, but deleting the formula in cells B2 and C2 would cause the 52000 to be spread over the remaining 34 months at 1529.41 per month. Regards Roger Govier John wrote: Sorry for the breifness, let me expand: 52000 is a total payment column. I want the divided 12 months to automatically put in the correct columns across three years. Basically I have the total amount and want to schedule it throughout the years. EXAMPLE: A B C D E F .... Total Jan Feb Mar Apr May .... 52000 --- ---- (place divided amount begining in Mar'05 through '06) Thanks, JOHN "Ian" wrote: Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Roger,
Just an update to say, UDAMAN. Thank you for this help. Everything works great. "John" wrote: Roger, No, to Struggling of Essex. This is my first time using this system. Thank you for your patience and knowledge. i will attempt to use your formula tomorrow. UDAMAN Roger. Keep up the good work. JOHN "Roger Govier" wrote: Hi John There was an almost similar question posed by "Struggling of Essex" in this group. Are you the same person? Anyway, basically your answer should be the same as the one I posted to him. I assume row 1 has headings, and all data will be entered in row 2 onward. Columns E onward, enter in the format Jan-05, Feb-05 etc. Using the fill handle will increment these for you automatically. Enter your Start Date in the format Apr-05, Jul-05 etc. In column B, duration just enter 12 (or number of months for that project) Don't enter a value in column D, enter this formula =DATE(YEAR(C2),MONTH(C2)+B2-1,DAY(C2)) In cell E2 enter the following =IF(AND(E$1=$C2,E$1<=$D2),$A2/$B2,"") Copy across for the maximum length of your project (I think it was 36 months for you) i.e. through F2:AO2 Copy E2:AO2 Paste through E3:E50 or as many projects as you wish to run. Regards Roger Govier John wrote: Thanks Roger. However, since I need the start and ending here's what I have done. A B C D E F G H I ETC... 50000 12MONTHS (START DATE) (END DATE) JAN FEB MAR APRIL MAY ETC... I need to take A1/B1 and put them in the correct 12 start and end columns using C1 and D1 dates. Some C1 dates start on Jan and some may start in May. Please help!! JOHN "Roger Govier" wrote: Hi John If your entries in B1:AK1 were set up as 01/01/05, 01/02/05 etc. but formatted Format CellsNumberCustom mmm-yy Then in B2 =A2/36 in C2 =($A$2-SUM($B$2:B2))/(37-(MONTH(C1)+12*(YEAR(C1)-YEAR($B$1)))) and copy across through D2:AK2 Obviously this does not tell the system where to start entering the values, but deleting the formula in cells B2 and C2 would cause the 52000 to be spread over the remaining 34 months at 1529.41 per month. Regards Roger Govier John wrote: Sorry for the breifness, let me expand: 52000 is a total payment column. I want the divided 12 months to automatically put in the correct columns across three years. Basically I have the total amount and want to schedule it throughout the years. EXAMPLE: A B C D E F .... Total Jan Feb Mar Apr May .... 52000 --- ---- (place divided amount begining in Mar'05 through '06) Thanks, JOHN "Ian" wrote: Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
Hi John
You're very welcome. Thanks for the feedback, its great to know you got it all to work satisfactorily. Regards Roger Govier John wrote: Roger, Just an update to say, UDAMAN. Thank you for this help. Everything works great. "John" wrote: Roger, No, to Struggling of Essex. This is my first time using this system. Thank you for your patience and knowledge. i will attempt to use your formula tomorrow. UDAMAN Roger. Keep up the good work. JOHN "Roger Govier" wrote: Hi John There was an almost similar question posed by "Struggling of Essex" in this group. Are you the same person? Anyway, basically your answer should be the same as the one I posted to him. I assume row 1 has headings, and all data will be entered in row 2 onward. Columns E onward, enter in the format Jan-05, Feb-05 etc. Using the fill handle will increment these for you automatically. Enter your Start Date in the format Apr-05, Jul-05 etc. In column B, duration just enter 12 (or number of months for that project) Don't enter a value in column D, enter this formula =DATE(YEAR(C2),MONTH(C2)+B2-1,DAY(C2)) In cell E2 enter the following =IF(AND(E$1=$C2,E$1<=$D2),$A2/$B2,"") Copy across for the maximum length of your project (I think it was 36 months for you) i.e. through F2:AO2 Copy E2:AO2 Paste through E3:E50 or as many projects as you wish to run. Regards Roger Govier John wrote: Thanks Roger. However, since I need the start and ending here's what I have done. A B C D E F G H I ETC... 50000 12MONTHS (START DATE) (END DATE) JAN FEB MAR APRIL MAY ETC... I need to take A1/B1 and put them in the correct 12 start and end columns using C1 and D1 dates. Some C1 dates start on Jan and some may start in May. Please help!! JOHN "Roger Govier" wrote: Hi John If your entries in B1:AK1 were set up as 01/01/05, 01/02/05 etc. but formatted Format CellsNumberCustom mmm-yy Then in B2 =A2/36 in C2 =($A$2-SUM($B$2:B2))/(37-(MONTH(C1)+12*(YEAR(C1)-YEAR($B$1)))) and copy across through D2:AK2 Obviously this does not tell the system where to start entering the values, but deleting the formula in cells B2 and C2 would cause the 52000 to be spread over the remaining 34 months at 1529.41 per month. Regards Roger Govier John wrote: Sorry for the breifness, let me expand: 52000 is a total payment column. I want the divided 12 months to automatically put in the correct columns across three years. Basically I have the total amount and want to schedule it throughout the years. EXAMPLE: A B C D E F .... Total Jan Feb Mar Apr May .... 52000 --- ---- (place divided amount begining in Mar'05 through '06) Thanks, JOHN "Ian" wrote: Another thought. If your 52000 is in cell A1 type =$A$1/12 in the September column and copy across. -- Ian -- "John" wrote in message ... I have a total amount of $52,000 and I want to show an equal amount in twelve columns which represent 12 months from sept 2005-sept 2006. My calendar goes for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the correct columns. |
| All times are GMT +1. The time now is 03:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com