Hi, I require a formula that expenses a prepayment up to an anniversary date
from the date it was paid on a monthly basis.

i.e. The due date is Feb 09 and the payment was made in Apr 08 of $800 so I
have a monthly expense of $80 which I want to start in Apr 08 and end in Jan
09 (I have columns for each month's expense).

At the moment I have the following formula which takes the expense and puts
it in from the beginning of the expense cycle (Col G), Col J is the month of
Expense. I need to nest a further if argument which stops the expense at the
end of the cycle as governed by Col F

=IF(J$4=$G6,$I6," ")

Any help would be appreciated.
Thanks

Hi Tuf,
What is in column G and I mentioned in the formula, can you send some
numbers reflecting what you want to achieve

"TUF" wrote:

Hi, I require a formula that expenses a prepayment up to an anniversary date
from the date it was paid on a monthly basis.

i.e. The due date is Feb 09 and the payment was made in Apr 08 of $800 so I
have a monthly expense of $80 which I want to start in Apr 08 and end in Jan
09 (I have columns for each month's expense).

At the moment I have the following formula which takes the expense and puts
it in from the beginning of the expense cycle (Col G), Col J is the month of
Expense. I need to nest a further if argument which stops the expense at the
end of the cycle as governed by Col F

=IF(J$4=$G6,$I6," ")

Any help would be appreciated.
Thanks

Hi Eduardo,

Col G represents the month of Expense was first incurred, i.e Apr 08
Col J is the beginning of the monthly columns, i.e Jan 08, Feb 08 etc
Col I is the monthly expense

So the current formula calculates which month the expense should start from,
however I want to include a further argument using Col F which represents
when the monthly expense is due to stop so that the monthly expense does not
go further than the column which represents the last month of the expense
cycle, i.e 6 months.

Thanks

Hi Tuf,
I understand now what you want to do, so your formula will be asuming your
first cell is J6 and the month are in J4

=IF(AND(J$4=$G5,J$4<=$F5),I5,"")

Formula will validate both conditions, first greater or equal to the month
you have incurred into the expense and the 2nd condition will validate the
expire date entered in column F

"TUF" wrote:

Hi Eduardo,

Col G represents the month of Expense was first incurred, i.e Apr 08
Col J is the beginning of the monthly columns, i.e Jan 08, Feb 08 etc
Col I is the monthly expense

So the current formula calculates which month the expense should start from,
however I want to include a further argument using Col F which represents
when the monthly expense is due to stop so that the monthly expense does not
go further than the column which represents the last month of the expense
cycle, i.e 6 months.

Thanks

Thanks

That's doen the trick

"Eduardo" wrote:

Hi Tuf,
I understand now what you want to do, so your formula will be asuming your
first cell is J6 and the month are in J4

=IF(AND(J$4=$G5,J$4<=$F5),I5,"")

Formula will validate both conditions, first greater or equal to the month
you have incurred into the expense and the 2nd condition will validate the
expire date entered in column F

"TUF" wrote:

Hi Eduardo,

Col G represents the month of Expense was first incurred, i.e Apr 08
Col J is the beginning of the monthly columns, i.e Jan 08, Feb 08 etc
Col I is the monthly expense

So the current formula calculates which month the expense should start from,
however I want to include a further argument using Col F which represents
when the monthly expense is due to stop so that the monthly expense does not
go further than the column which represents the last month of the expense
cycle, i.e 6 months.

Thanks

Hi Tuf, if it helped you please check the box where it says the answer was
helpful. thank you

"TUF" wrote:

Thanks

That's doen the trick

"Eduardo" wrote:

Hi Tuf,
I understand now what you want to do, so your formula will be asuming your
first cell is J6 and the month are in J4

=IF(AND(J$4=$G5,J$4<=$F5),I5,"")

Formula will validate both conditions, first greater or equal to the month
you have incurred into the expense and the 2nd condition will validate the
expire date entered in column F

"TUF" wrote:

Hi Eduardo,

Col G represents the month of Expense was first incurred, i.e Apr 08
Col J is the beginning of the monthly columns, i.e Jan 08, Feb 08 etc
Col I is the monthly expense

So the current formula calculates which month the expense should start from,
however I want to include a further argument using Col F which represents
when the monthly expense is due to stop so that the monthly expense does not
go further than the column which represents the last month of the expense
cycle, i.e 6 months.

Thanks