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

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

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

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

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

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

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

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