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

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

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

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 -

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 -

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 -