Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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 "Burt" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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 wrote: 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 "Burt" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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" wrote: 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 wrote: 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 "Burt" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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))=DAT E(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))=DAT E(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 Burt wrote: 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" wrote: 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 wrote: 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 "Burt" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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 "vezerid" wrote in message oups.com... 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))=DAT E(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))=DAT E(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 Burt wrote: 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" wrote: 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 wrote: 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 "Burt" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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 Roger Govier wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
....what can I say. Genius!!
Thank you so much!! "vezerid" wrote: 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 Roger Govier wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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? "Burt" wrote: ...what can I say. Genius!! Thank you so much!! "vezerid" wrote: 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 Roger Govier wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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? "Burt" wrote: 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? "Burt" wrote: ...what can I say. Genius!! Thank you so much!! "vezerid" wrote: 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 Roger Govier wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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 Burt wrote: 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? "Burt" wrote: 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? "Burt" wrote: ...what can I say. Genius!! Thank you so much!! "vezerid" wrote: 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 Roger Govier wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement
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.... "vezerid" wrote: 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 Burt wrote: 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? "Burt" wrote: 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? "Burt" wrote: ...what can I say. Genius!! Thank you so much!! "vezerid" wrote: 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 Roger Govier wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL concatenation statement | Excel Discussion (Misc queries) | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
What statement to use? | Excel Worksheet Functions |