Excel Function .. I sthere one out there somewhere?

[Finance Guru]

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

 

[Pete_UK]

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 F22. Then copy E22 down for as many rows
as you have. In Q2 you can enter this formula:

=D2-SUM(E22)

and copy this down. Format the cells in E:Q as currency.

Hope this helps.

Pete

 

[Finance Guru]

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]

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

 

[Finance Guru]

It's all so easy when it is written like that.
Many thanks again
FG

 

[Pete_UK]

You're welcome.

Pete

It's all so easy when it is written like that.
Many thanks again
FG








- Show quoted text -