Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Allocation Dinesh Excel Worksheet Functions 4 June 28th 07 10:06 PM
Constant loan payments vs. constant payments of principal lalli945 Excel Worksheet Functions 3 December 20th 06 11:33 PM
F-key allocation HELLBOY787 Excel Discussion (Misc queries) 1 June 30th 05 11:21 AM
re-allocation problem Sienayr Excel Discussion (Misc queries) 1 March 21st 05 11:03 PM
How do I forecast future payments by analyzing past payments? CeeBee Excel Worksheet Functions 1 March 17th 05 07:43 PM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"