Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2.
Using the day function, I have the number of days in that month, which is 31, in cell E4. I have a start date (1/29/2008) in A5 and an End Date (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) in D5. I am trying to allocate that $120,000 over the entire time interval, from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because there are 353 days between the start date and the end date and there are two days between 1/29/2008 and 1/31/2008. Based on the same logic, I would expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I would expect to see $5,439 in Q5. Then, when I sum all the data elements on this row, I would like to see a total of $120,000, which indicates that all payments have been made and accounted for. After all this, I would go to E5, and fill-down a few rows, and all cells should populate with the correct payments/amounts. I am using this function: =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) It is close, but not quite correct. Id appreciate any and all assistance. This is somewhat complicated for me, only because I cant seem to get my mind into it. In all honesty, I dont think it is all that difficult though. Thanks!! -- RyGuy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
<Based on the same logic, I would expect to see $9,858 in F5.
That bit I don't understand. What is the logic? -- Kind regards, Niek Otten Microsoft MVP - Excel "ryguy7272" wrote in message ... | Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2. | Using the day function, I have the number of days in that month, which is 31, | in cell E4. I have a start date (1/29/2008) in A5 and an End Date | (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) | in D5. I am trying to allocate that $120,000 over the entire time interval, | from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because | there are 353 days between the start date and the end date and there are two | days between 1/29/2008 and 1/31/2008. Based on the same logic, I would | expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I | would expect to see $5,439 in Q5. Then, when I sum all the data elements on | this row, I would like to see a total of $120,000, which indicates that all | payments have been made and accounted for. After all this, I would go to E5, | and fill-down a few rows, and all cells should populate with the correct | payments/amounts. | | I am using this function: | =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) | | It is close, but not quite correct. I'd appreciate any and all assistance. | This is somewhat complicated for me, only because I can't seem to get my mind | into it. In all honesty, I don't think it is all that difficult though. | | Thanks!! | | | -- | RyGuy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
E2:AB2 contains dates, with the end of the month in each column.
For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009 S4:AB4 contains the number of days in each month. E4 = 31, F4 = 29...AB4 = 31 A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000 A5 = 1/29/08 B5 = 1/15/09 C5 = 353 (1/15/09 - 1/29/08) + 1 D5 = $120,000 E5 = $680 The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to 1/31/08 is 2. This is multiplied by the total amount of revenue collected divided by the total number of days over which the revenue is collected (120,000/353). I think I am close to a solution, but I can't quite resolve this thing. -- RyGuy "Niek Otten" wrote: <Based on the same logic, I would expect to see $9,858 in F5. That bit I don't understand. What is the logic? -- Kind regards, Niek Otten Microsoft MVP - Excel "ryguy7272" wrote in message ... | Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2. | Using the day function, I have the number of days in that month, which is 31, | in cell E4. I have a start date (1/29/2008) in A5 and an End Date | (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) | in D5. I am trying to allocate that $120,000 over the entire time interval, | from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because | there are 353 days between the start date and the end date and there are two | days between 1/29/2008 and 1/31/2008. Based on the same logic, I would | expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I | would expect to see $5,439 in Q5. Then, when I sum all the data elements on | this row, I would like to see a total of $120,000, which indicates that all | payments have been made and accounted for. After all this, I would go to E5, | and fill-down a few rows, and all cells should populate with the correct | payments/amounts. | | I am using this function: | =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) | | It is close, but not quite correct. I'd appreciate any and all assistance. | This is somewhat complicated for me, only because I can't seem to get my mind | into it. In all honesty, I don't think it is all that difficult though. | | Thanks!! | | | -- | RyGuy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
You might try
=($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4) Note that you've added 1 in your C5 formula (so effectively counted start and end dates) but in your E5 formula you haven't added an extra day, so you need to add an extra day at the end. -- David Biddulph "ryguy7272" wrote in message ... E2:AB2 contains dates, with the end of the month in each column. For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009 S4:AB4 contains the number of days in each month. E4 = 31, F4 = 29...AB4 = 31 A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000 A5 = 1/29/08 B5 = 1/15/09 C5 = 353 (1/15/09 - 1/29/08) + 1 D5 = $120,000 E5 = $680 The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to 1/31/08 is 2. This is multiplied by the total amount of revenue collected divided by the total number of days over which the revenue is collected (120,000/353). I think I am close to a solution, but I can't quite resolve this thing. -- RyGuy "Niek Otten" wrote: <Based on the same logic, I would expect to see $9,858 in F5. That bit I don't understand. What is the logic? -- Kind regards, Niek Otten Microsoft MVP - Excel "ryguy7272" wrote in message ... | Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2. | Using the day function, I have the number of days in that month, which is 31, | in cell E4. I have a start date (1/29/2008) in A5 and an End Date | (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) | in D5. I am trying to allocate that $120,000 over the entire time interval, | from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because | there are 353 days between the start date and the end date and there are two | days between 1/29/2008 and 1/31/2008. Based on the same logic, I would | expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I | would expect to see $5,439 in Q5. Then, when I sum all the data elements on | this row, I would like to see a total of $120,000, which indicates that all | payments have been made and accounted for. After all this, I would go to E5, | and fill-down a few rows, and all cells should populate with the correct | payments/amounts. | | I am using this function: | =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) | | It is close, but not quite correct. I'd appreciate any and all assistance. | This is somewhat complicated for me, only because I can't seem to get my mind | into it. In all honesty, I don't think it is all that difficult though. | | Thanks!! | | | -- | RyGuy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
Sorry. Ihadn't trapped for a start date beyond the first column.
Change my suuggestion to =($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4)*($A5<=E$2) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You might try =($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4) Note that you've added 1 in your C5 formula (so effectively counted start and end dates) but in your E5 formula you haven't added an extra day, so you need to add an extra day at the end. -- David Biddulph "ryguy7272" wrote in message ... E2:AB2 contains dates, with the end of the month in each column. For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009 S4:AB4 contains the number of days in each month. E4 = 31, F4 = 29...AB4 = 31 A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000 A5 = 1/29/08 B5 = 1/15/09 C5 = 353 (1/15/09 - 1/29/08) + 1 D5 = $120,000 E5 = $680 The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to 1/31/08 is 2. This is multiplied by the total amount of revenue collected divided by the total number of days over which the revenue is collected (120,000/353). I think I am close to a solution, but I can't quite resolve this thing. -- RyGuy "Niek Otten" wrote: <Based on the same logic, I would expect to see $9,858 in F5. That bit I don't understand. What is the logic? -- Kind regards, Niek Otten Microsoft MVP - Excel "ryguy7272" wrote in message ... | Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2. | Using the day function, I have the number of days in that month, which is 31, | in cell E4. I have a start date (1/29/2008) in A5 and an End Date | (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) | in D5. I am trying to allocate that $120,000 over the entire time interval, | from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because | there are 353 days between the start date and the end date and there are two | days between 1/29/2008 and 1/31/2008. Based on the same logic, I would | expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I | would expect to see $5,439 in Q5. Then, when I sum all the data elements on | this row, I would like to see a total of $120,000, which indicates that all | payments have been made and accounted for. After all this, I would go to E5, | and fill-down a few rows, and all cells should populate with the correct | payments/amounts. | | I am using this function: | =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) | | It is close, but not quite correct. I'd appreciate any and all assistance. | This is somewhat complicated for me, only because I can't seem to get my mind | into it. In all honesty, I don't think it is all that difficult though. | | Thanks!! | | | -- | RyGuy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
No problem can be solved from the same level of consciousness that created it.
Albert Einstein- Thank you so much David!! I made a small modification, and went with this: =IF(AND($A5E$2,$B5E$2),0,($D5/$C5)*(MIN($B5,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4)) Regards, Ryan--- -- RyGuy "David Biddulph" wrote: You might try =($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4) Note that you've added 1 in your C5 formula (so effectively counted start and end dates) but in your E5 formula you haven't added an extra day, so you need to add an extra day at the end. -- David Biddulph "ryguy7272" wrote in message ... E2:AB2 contains dates, with the end of the month in each column. For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009 S4:AB4 contains the number of days in each month. E4 = 31, F4 = 29...AB4 = 31 A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000 A5 = 1/29/08 B5 = 1/15/09 C5 = 353 (1/15/09 - 1/29/08) + 1 D5 = $120,000 E5 = $680 The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to 1/31/08 is 2. This is multiplied by the total amount of revenue collected divided by the total number of days over which the revenue is collected (120,000/353). I think I am close to a solution, but I can't quite resolve this thing. -- RyGuy "Niek Otten" wrote: <Based on the same logic, I would expect to see $9,858 in F5. That bit I don't understand. What is the logic? -- Kind regards, Niek Otten Microsoft MVP - Excel "ryguy7272" wrote in message ... | Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2. | Using the day function, I have the number of days in that month, which is 31, | in cell E4. I have a start date (1/29/2008) in A5 and an End Date | (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) | in D5. I am trying to allocate that $120,000 over the entire time interval, | from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because | there are 353 days between the start date and the end date and there are two | days between 1/29/2008 and 1/31/2008. Based on the same logic, I would | expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I | would expect to see $5,439 in Q5. Then, when I sum all the data elements on | this row, I would like to see a total of $120,000, which indicates that all | payments have been made and accounted for. After all this, I would go to E5, | and fill-down a few rows, and all cells should populate with the correct | payments/amounts. | | I am using this function: | =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) | | It is close, but not quite correct. I'd appreciate any and all assistance. | This is somewhat complicated for me, only because I can't seem to get my mind | into it. In all honesty, I don't think it is all that difficult though. | | Thanks!! | | | -- | RyGuy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
I assume that you've deleted the +1 from your formula in C5?
-- David Biddulph "ryguy7272" wrote in message ... No problem can be solved from the same level of consciousness that created it. Albert Einstein- Thank you so much David!! I made a small modification, and went with this: =IF(AND($A5E$2,$B5E$2),0,($D5/$C5)*(MIN($B5,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4)) Regards, Ryan--- "David Biddulph" wrote: You might try =($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4) Note that you've added 1 in your C5 formula (so effectively counted start and end dates) but in your E5 formula you haven't added an extra day, so you need to add an extra day at the end. -- David Biddulph "ryguy7272" wrote in message ... E2:AB2 contains dates, with the end of the month in each column. For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009 S4:AB4 contains the number of days in each month. E4 = 31, F4 = 29...AB4 = 31 A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000 A5 = 1/29/08 B5 = 1/15/09 C5 = 353 (1/15/09 - 1/29/08) + 1 D5 = $120,000 E5 = $680 The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to 1/31/08 is 2. This is multiplied by the total amount of revenue collected divided by the total number of days over which the revenue is collected (120,000/353). I think I am close to a solution, but I can't quite resolve this thing. -- RyGuy "Niek Otten" wrote: <Based on the same logic, I would expect to see $9,858 in F5. That bit I don't understand. What is the logic? -- Kind regards, Niek Otten Microsoft MVP - Excel "ryguy7272" wrote in message ... | Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2. | Using the day function, I have the number of days in that month, which is 31, | in cell E4. I have a start date (1/29/2008) in A5 and an End Date | (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) | in D5. I am trying to allocate that $120,000 over the entire time interval, | from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because | there are 353 days between the start date and the end date and there are two | days between 1/29/2008 and 1/31/2008. Based on the same logic, I would | expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I | would expect to see $5,439 in Q5. Then, when I sum all the data elements on | this row, I would like to see a total of $120,000, which indicates that all | payments have been made and accounted for. After all this, I would go to E5, | and fill-down a few rows, and all cells should populate with the correct | payments/amounts. | | I am using this function: | =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) | | It is close, but not quite correct. I'd appreciate any and all assistance. | This is somewhat complicated for me, only because I can't seem to get my mind | into it. In all honesty, I don't think it is all that difficult though. | | Thanks!! | | | -- | RyGuy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Somewhat Complex: Allocation of Payments
Indeed, I did delete it! Excel is quite remarkable. I am constantly amazed
at the types of things one can do with a little creative thinking. Thanks again Dave!!! -- RyGuy "David Biddulph" wrote: I assume that you've deleted the +1 from your formula in C5? -- David Biddulph "ryguy7272" wrote in message ... No problem can be solved from the same level of consciousness that created it. Albert Einstein- Thank you so much David!! I made a small modification, and went with this: =IF(AND($A5E$2,$B5E$2),0,($D5/$C5)*(MIN($B5,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4)) Regards, Ryan--- "David Biddulph" wrote: You might try =($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5=E$2-E$4) Note that you've added 1 in your C5 formula (so effectively counted start and end dates) but in your E5 formula you haven't added an extra day, so you need to add an extra day at the end. -- David Biddulph "ryguy7272" wrote in message ... E2:AB2 contains dates, with the end of the month in each column. For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009 S4:AB4 contains the number of days in each month. E4 = 31, F4 = 29...AB4 = 31 A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000 A5 = 1/29/08 B5 = 1/15/09 C5 = 353 (1/15/09 - 1/29/08) + 1 D5 = $120,000 E5 = $680 The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to 1/31/08 is 2. This is multiplied by the total amount of revenue collected divided by the total number of days over which the revenue is collected (120,000/353). I think I am close to a solution, but I can't quite resolve this thing. -- RyGuy "Niek Otten" wrote: <Based on the same logic, I would expect to see $9,858 in F5. That bit I don't understand. What is the logic? -- Kind regards, Niek Otten Microsoft MVP - Excel "ryguy7272" wrote in message ... | Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2. | Using the day function, I have the number of days in that month, which is 31, | in cell E4. I have a start date (1/29/2008) in A5 and an End Date | (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000) | in D5. I am trying to allocate that $120,000 over the entire time interval, | from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because | there are 353 days between the start date and the end date and there are two | days between 1/29/2008 and 1/31/2008. Based on the same logic, I would | expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I | would expect to see $5,439 in Q5. Then, when I sum all the data elements on | this row, I would like to see a total of $120,000, which indicates that all | payments have been made and accounted for. After all this, I would go to E5, | and fill-down a few rows, and all cells should populate with the correct | payments/amounts. | | I am using this function: | =IF(AND($A5E$2,$B5E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5)))) | | It is close, but not quite correct. I'd appreciate any and all assistance. | This is somewhat complicated for me, only because I can't seem to get my mind | into it. In all honesty, I don't think it is all that difficult though. | | Thanks!! | | | -- | RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allocation | Excel Worksheet Functions | |||
Constant loan payments vs. constant payments of principal | Excel Worksheet Functions | |||
F-key allocation | Excel Discussion (Misc queries) | |||
re-allocation problem | Excel Discussion (Misc queries) | |||
How do I forecast future payments by analyzing past payments? | Excel Worksheet Functions |