IF statement

[Burt]

This may be too complicated to explain without showing the financial
spreadsheet...but here goes.

Firstly I have a start date column and end date column for Projects. There
is also a contribution column which shows the total amount of contribution
for that project (e.g. £5,900). Lastly, I have 4 columns which are the
financial years (05/06, 06/07 etc) which at the moment are blank.

I want to create a formula that checks the start and end date and then
apportions the contribution across the financial years.

e.g if the start date and end date was 01/09/05 and 01/09/2006 respectively
and the contribution was £2000 i want a formula in the financial year columns
that splits the money into the relevant year. in this case £1000 in the
05/06 column and £1000 in the 06/07 column.

Can it be done?

 

[Burt]

I forgot to add the the amount split wouldnt be not be equal.

an example would be:

£5000 contribution for a duration of 18 month starting 01/01/2006 means I
need the spread to be - £1944.44 in year 05/06 (7 months worth) and £3055.56
in 06/07 (11 months worth) as the financial year end is 31/07/2006 hence the
reason for the split.

This could save days of work if it can be done

 

[vezerid]

Burt,

by which criterion do you want the money to be alotted to half-years?
By the number of days/365? So, in a project from 12/17/2004 until
12/31/2005, with a total of 379 days, you want 14/379 to go to 2004 and
365/379 to go to 2005?

Kostis Vezerides

 

[Burt]

thats right. In your example it would be 14/379 to go to 2004 and
365/379 to go to 2005?

the only difference is that i am using financial years e.g.

05/06 = 01/08/2005 to 31/07/2006
06/07 = 01/08/2006 to 31/07/2007
07/08 = 01/08/2007 to 31/07/2008

 

[vezerid]

Burt,

I am assuming the following layout:

Cells A2:Ax contain start dates.
Cells B2:Bx contain end dates.
Cells C2:Cx contain project budget.
Cells D1:I1 contain the numbers 2003, 2004, etc. 2003 stands for the
fiscal year 8/1/2003-7/31/2004. Clearly you can extend this range for
more years.

In D2 you put the formula:
=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=DATE(E$1,8,1))*(ROW(INDIRECT($A2&":"&$B2))<DATE(E$1+1,8,1)))

Copy down and across as necessary.

If you insist on having different labels (e.g. 03/04) you can use the
following variant

=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=DATE(LEFT(E$1,2)+2000,8,1))*(ROW(INDIRECT($A2&":"&$B2))<DATE(LEFT(E$1,2)+2000+1,8,1)))

Or, if you prefer the simpler formula:
You leave the headers as I suggest and start everything else from row
3. Leave these numbers in row 1, hide row 1 and supply your labels in
row 2 (visible).

HTH
Kostis Vezerides

 

[Roger Govier]

Hi Kostis

Very nice solution!
There might be a typo though as I think the formula in D2 needs to start
with dates from Year D1 not E1

=$C2/($B2-$A2+1)*
SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=DATE(D$1,8,1))*
(ROW(INDIRECT($A2&":"&$B2))<DATE(D$1+1,8,1)))

--
Regards

Roger Govier


Burt,

I am assuming the following layout:

Cells A2:Ax contain start dates.
Cells B2:Bx contain end dates.
Cells C2:Cx contain project budget.
Cells D1:I1 contain the numbers 2003, 2004, etc. 2003 stands for the
fiscal year 8/1/2003-7/31/2004. Clearly you can extend this range for
more years.

In D2 you put the formula:
=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=DATE(E$1,8,1))*(ROW(INDIRECT($A2&":"&$B2))<DATE(E$1+1,8,1)))

Copy down and across as necessary.

If you insist on having different labels (e.g. 03/04) you can use the
following variant

=$C2/($B2-$A2+1)*SUMPRODUCT((ROW(INDIRECT($A2&":"&$B2))>=DATE(LEFT(E$1,2)+2000,8,1))*(ROW(INDIRECT($A2&":"&$B2))<DATE(LEFT(E$1,2)+2000+1,8,1)))

Or, if you prefer the simpler formula:
You leave the headers as I suggest and start everything else from row
3. Leave these numbers in row 1, hide row 1 and supply your labels in
row 2 (visible).

HTH
Kostis Vezerides

 

[vezerid]

Roger,

Thank you, I am flattered <s>.
Yes, you are right, I had created an extra column because for a while I
had a discrepancy in the total number of days (forgot the +1). The OP
should paste your formula.

Regards,
Kostis

 

[Burt]

....what can I say. Genius!!

Thank you so much!!

Roger,

Thank you, I am flattered <s>.
Yes, you are right, I had created an extra column because for a while I
had a discrepancy in the total number of days (forgot the +1). The OP
should paste your formula.

Regards,
Kostis

 

[Burt]

I thought it had worked but theres a slight problem. When I copy the formula
down, there is a secton in my list for which the formula doesnt work and
returns a #Ref! error. I cant see why it would do this as its worked
perfectly for all the others. The rows in question are exatly the same as
those for which the formula has worked.

Any suggestions?

...what can I say. Genius!!

Thank you so much!!

 

[Burt]

I figured out the problem. For some reason if I retype the dates on the rows
that werent working, the formula suddenly works again.

Is there anyway of quickly updating all the dates without manually retyping
them? and why did this happen in the first place?

 

[vezerid]

The #REF! error probably comes from INDIRECT. This means that these
cells did not really contain a date. Either they had been set to Text
format (unlikely, since retyping corrected the problem), or you thought
they contained a date and they actually did not.

Things to check: Are any dates left aligned istead of right-aligned? Is
there any chance someone changed the Regional Settings (Control Panel)
from european (dd-mm-yy) to US (mm-dd-yy) or vice versa? Were some of
these dates imported from another application?

Ordinarily you should not have this problem in the long run. If new
dates added are real dates it should keep working. The quick way to
correct text->date is to copy a blank cell, then select all dates and
Edit|Paste Special...|Add.

HTH
Kostis

 

[romelsb]

HI good guys...hope to participate in your money wise studies....i got a
workbook that may appear strange but the bottomline is there, it may work for
us...I am not fund using the indirect function because i go with direct
process to easily trace errors. Do you work out this type of worksheet under
a circular formulation, if you do i can share this to u and vis a vis....