Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function .. I sthere one out there somewhere?
FROM TO PERIODS COST JAN FEB MAR APR C/FWD
£GBP 01/08/2007 31/07/2008 12 8,956.00 10/02/2007 09/02/2008 12 27,443.00 05/05/2007 15/10/2007 5 2998.63 Hello, I am trying to find a function that will do the following Based on the month in the from Column ( eg. 08 )spread the cost (8,956) over the periods(12) and where the periods exceed Dec ( Calendar year ) put the the unallocated amount in C/Fwd col. Based on the Month in the From Column it should start to populate the column respective to the start month eg. 01/08/2007 would start in the colum headed August,the start month eg. 05/05/07 would start in the column headed May,and so on. I know the talent is out there .. but can anyone assist. I am running Excel 2003 Many thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function .. I sthere one out there somewhere?
I presume you have the 12 months in columns E to P, with your C/FWD in
column Q, and that your data begins in row 2. Put this formula in E2: =IF(OR(MONTH($A2)COLUMN(A$1),MONTH($A2)+$C2-1<COLUMN(A$1)),0,$D2/$C2) and copy this across into F2:P2. Then copy E2:P2 down for as many rows as you have. In Q2 you can enter this formula: =D2-SUM(E2:P2) and copy this down. Format the cells in E:Q as currency. Hope this helps. Pete On Nov 14, 11:21 am, Finance Guru wrote: FROM TO PERIODS COST JAN FEB MAR APR C/FWD £GBP 01/08/2007 31/07/2008 12 8,956.00 10/02/2007 09/02/2008 12 27,443.00 05/05/2007 15/10/2007 5 2998.63 Hello, I am trying to find a function that will do the following Based on the month in the from Column ( eg. 08 )spread the cost (8,956) over the periods(12) and where the periods exceed Dec ( Calendar year ) put the the unallocated amount in C/Fwd col. Based on the Month in the From Column it should start to populate the column respective to the start month eg. 01/08/2007 would start in the colum headed August,the start month eg. 05/05/07 would start in the column headed May,and so on. I know the talent is out there .. but can anyone assist. I am running Excel 2003 Many thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function .. I sthere one out there somewhere?
Hi Pete - Many thanks for such a prompt reply. It didn't work in my w/sheet
because the columns are not e - p they are sort of up in the AE - AQ's realms of the W/shhet BUT I did try it on a virgin w/sheet and it works perfectly. Thank you,Thank you,Thank you. Just one question, if you have the time - could you explain in laymans terms what the various bits of the function do eg. COLUMN(A$1),etc,etc - because this is the first time i seen the COLUMN function. Also If I need to use the function again,which I will,I won't have to trouble the discussion groups, and of copurse it may help anyone else looking in. Thanks again. FG "Pete_UK" wrote: I presume you have the 12 months in columns E to P, with your C/FWD in column Q, and that your data begins in row 2. Put this formula in E2: =IF(OR(MONTH($A2)COLUMN(A$1),MONTH($A2)+$C2-1<COLUMN(A$1)),0,$D2/$C2) and copy this across into F2:P2. Then copy E2:P2 down for as many rows as you have. In Q2 you can enter this formula: =D2-SUM(E2:P2) and copy this down. Format the cells in E:Q as currency. Hope this helps. Pete On Nov 14, 11:21 am, Finance Guru wrote: FROM TO PERIODS COST JAN FEB MAR APR C/FWD £GBP 01/08/2007 31/07/2008 12 8,956.00 10/02/2007 09/02/2008 12 27,443.00 05/05/2007 15/10/2007 5 2998.63 Hello, I am trying to find a function that will do the following Based on the month in the from Column ( eg. 08 )spread the cost (8,956) over the periods(12) and where the periods exceed Dec ( Calendar year ) put the the unallocated amount in C/Fwd col. Based on the Month in the From Column it should start to populate the column respective to the start month eg. 01/08/2007 would start in the colum headed August,the start month eg. 05/05/07 would start in the column headed May,and so on. I know the talent is out there .. but can anyone assist. I am running Excel 2003 Many thanks in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function .. I sthere one out there somewhere?
Thanks for the feedback - glad to hear it worked. You should be able
to adjust the cell references to suit your sheet. When you are in the first month column (E in my case), you want that to refer to month 1, similarly in column F you want to refer to month 2. The function COLUMN(A$1) returns the column number of the reference, i.e. 1. As this gets copied across it becomes COLUMN(B$1), COLUMN(C$1) etc, which in turn returns the numbers 2, 3 etc. Hence it returns the appropriate month number for the column you are in. The function includes an IF with 2 OR conditions - if the month (from the date) is greater than the column month, then we don't want to start allocating the funds, so return zero. Similarly, if we are past the number of months that the funds should be divided over, then also return zero (the second of the conditions). This is more noticeable in the third example that you posted. Hope this explains things a bit further. Pete On Nov 14, 1:55 pm, Finance Guru wrote: Hi Pete - Many thanks for such a prompt reply. It didn't work in my w/sheet because the columns are not e - p they are sort of up in the AE - AQ's realms of the W/shhet BUT I did try it on a virgin w/sheet and it works perfectly. Thank you,Thank you,Thank you. Just one question, if you have the time - could you explain in laymans terms what the various bits of the function do eg. COLUMN(A$1),etc,etc - because this is the first time i seen the COLUMN function. Also If I need to use the function again,which I will,I won't have to trouble the discussion groups, and of copurse it may help anyone else looking in. Thanks again. FG "Pete_UK" wrote: I presume you have the 12 months in columns E to P, with your C/FWD in column Q, and that your data begins in row 2. Put this formula in E2: =IF(OR(MONTH($A2)COLUMN(A$1),MONTH($A2)+$C2-1<COLUMN(A$1)),0,$D2/$C2) and copy this across into F2:P2. Then copy E2:P2 down for as many rows as you have. In Q2 you can enter this formula: =D2-SUM(E2:P2) and copy this down. Format the cells in E:Q as currency. Hope this helps. Pete On Nov 14, 11:21 am, Finance Guru wrote: FROM TO PERIODS COST JAN FEB MAR APR C/FWD £GBP 01/08/2007 31/07/2008 12 8,956.00 10/02/2007 09/02/2008 12 27,443.00 05/05/2007 15/10/2007 5 2998.63 Hello, I am trying to find a function that will do the following Based on the month in the from Column ( eg. 08 )spread the cost (8,956) over the periods(12) and where the periods exceed Dec ( Calendar year ) put the the unallocated amount in C/Fwd col. Based on the Month in the From Column it should start to populate the column respective to the start month eg. 01/08/2007 would start in the colum headed August,the start month eg. 05/05/07 would start in the column headed May,and so on. I know the talent is out there .. but can anyone assist. I am running Excel 2003 Many thanks in advance- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function .. I sthere one out there somewhere?
It's all so easy when it is written like that.
Many thanks again FG "Pete_UK" wrote: Thanks for the feedback - glad to hear it worked. You should be able to adjust the cell references to suit your sheet. When you are in the first month column (E in my case), you want that to refer to month 1, similarly in column F you want to refer to month 2. The function COLUMN(A$1) returns the column number of the reference, i.e. 1. As this gets copied across it becomes COLUMN(B$1), COLUMN(C$1) etc, which in turn returns the numbers 2, 3 etc. Hence it returns the appropriate month number for the column you are in. The function includes an IF with 2 OR conditions - if the month (from the date) is greater than the column month, then we don't want to start allocating the funds, so return zero. Similarly, if we are past the number of months that the funds should be divided over, then also return zero (the second of the conditions). This is more noticeable in the third example that you posted. Hope this explains things a bit further. Pete On Nov 14, 1:55 pm, Finance Guru wrote: Hi Pete - Many thanks for such a prompt reply. It didn't work in my w/sheet because the columns are not e - p they are sort of up in the AE - AQ's realms of the W/shhet BUT I did try it on a virgin w/sheet and it works perfectly. Thank you,Thank you,Thank you. Just one question, if you have the time - could you explain in laymans terms what the various bits of the function do eg. COLUMN(A$1),etc,etc - because this is the first time i seen the COLUMN function. Also If I need to use the function again,which I will,I won't have to trouble the discussion groups, and of copurse it may help anyone else looking in. Thanks again. FG "Pete_UK" wrote: I presume you have the 12 months in columns E to P, with your C/FWD in column Q, and that your data begins in row 2. Put this formula in E2: =IF(OR(MONTH($A2)COLUMN(A$1),MONTH($A2)+$C2-1<COLUMN(A$1)),0,$D2/$C2) and copy this across into F2:P2. Then copy E2:P2 down for as many rows as you have. In Q2 you can enter this formula: =D2-SUM(E2:P2) and copy this down. Format the cells in E:Q as currency. Hope this helps. Pete On Nov 14, 11:21 am, Finance Guru wrote: FROM TO PERIODS COST JAN FEB MAR APR C/FWD £GBP 01/08/2007 31/07/2008 12 8,956.00 10/02/2007 09/02/2008 12 27,443.00 05/05/2007 15/10/2007 5 2998.63 Hello, I am trying to find a function that will do the following Based on the month in the from Column ( eg. 08 )spread the cost (8,956) over the periods(12) and where the periods exceed Dec ( Calendar year ) put the the unallocated amount in C/Fwd col. Based on the Month in the From Column it should start to populate the column respective to the start month eg. 01/08/2007 would start in the colum headed August,the start month eg. 05/05/07 would start in the column headed May,and so on. I know the talent is out there .. but can anyone assist. I am running Excel 2003 Many thanks in advance- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function .. I sthere one out there somewhere?
You're welcome.
Pete On Nov 14, 4:04 pm, Finance Guru wrote: It's all so easy when it is written like that. Many thanks again FG "Pete_UK" wrote: Thanks for the feedback - glad to hear it worked. You should be able to adjust the cell references to suit your sheet. When you are in the first month column (E in my case), you want that to refer to month 1, similarly in column F you want to refer to month 2. The function COLUMN(A$1) returns the column number of the reference, i.e. 1. As this gets copied across it becomes COLUMN(B$1), COLUMN(C$1) etc, which in turn returns the numbers 2, 3 etc. Hence it returns the appropriate month number for the column you are in. The function includes an IF with 2 OR conditions - if the month (from the date) is greater than the column month, then we don't want to start allocating the funds, so return zero. Similarly, if we are past the number of months that the funds should be divided over, then also return zero (the second of the conditions). This is more noticeable in the third example that you posted. Hope this explains things a bit further. Pete On Nov 14, 1:55 pm, Finance Guru wrote: Hi Pete - Many thanks for such a prompt reply. It didn't work in my w/sheet because the columns are not e - p they are sort of up in the AE - AQ's realms of the W/shhet BUT I did try it on a virgin w/sheet and it works perfectly. Thank you,Thank you,Thank you. Just one question, if you have the time - could you explain in laymans terms what the various bits of the function do eg. COLUMN(A$1),etc,etc - because this is the first time i seen the COLUMN function. Also If I need to use the function again,which I will,I won't have to trouble the discussion groups, and of copurse it may help anyone else looking in. Thanks again. FG "Pete_UK" wrote: I presume you have the 12 months in columns E to P, with your C/FWD in column Q, and that your data begins in row 2. Put this formula in E2: =IF(OR(MONTH($A2)COLUMN(A$1),MONTH($A2)+$C2-1<COLUMN(A$1)),0,$D2/$C2) and copy this across into F2:P2. Then copy E2:P2 down for as many rows as you have. In Q2 you can enter this formula: =D2-SUM(E2:P2) and copy this down. Format the cells in E:Q as currency. Hope this helps. Pete On Nov 14, 11:21 am, Finance Guru wrote: FROM TO PERIODS COST JAN FEB MAR APR C/FWD £GBP 01/08/2007 31/07/2008 12 8,956.00 10/02/2007 09/02/2008 12 27,443.00 05/05/2007 15/10/2007 5 2998.63 Hello, I am trying to find a function that will do the following Based on the month in the from Column ( eg. 08 )spread the cost (8,956) over the periods(12) and where the periods exceed Dec ( Calendar year ) put the the unallocated amount in C/Fwd col. Based on the Month in the From Column it should start to populate the column respective to the start month eg. 01/08/2007 would start in the colum headed August,the start month eg. 05/05/07 would start in the column headed May,and so on. I know the talent is out there .. but can anyone assist. I am running Excel 2003 Many thanks in advance- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
Excel 2002: Auto Sum function not working in large Excel file | Excel Discussion (Misc queries) | |||
challenge! javascript function into excel function | Excel Worksheet Functions | |||
Excel Workday Function with another function | Excel Discussion (Misc queries) | |||
Can you nest a MID function within a IF function in Excel | Excel Worksheet Functions |