formula based on expiration

  • Thread starter Thread starter Michael J. Malinsky
  • Start date Start date
M

Michael J. Malinsky

Let's say I have:

Monthly Expires 2004 2005 2006
2007 2008 Thereafter
Rent
500 12/31/2004 80,496 80,496
200 10/31/2005 2,400 2,000

I'm trying to create a formula that will automatically calculate the total
payments for each of the next five years and thereafter. So, for example, a
formula that would know to calculate 12 months of rent for 2004 and 10
months for 2005 for the 200/month item. I've tried various things to no
avail.

TIA
 
Hi Michael

The following formula assumes your headers are in row 1, and your first rent value is in A2. If so then enter this formula in C2 and copy down and over

=IF(YEAR($B2)>C$1,12*$A2,IF(YEAR($B2)=C$1,MONTH($B2)*$A2,"")

The logic runs like this
If the expiration year is greater the the column year then 12 times the rent value
If the expiration year is equal to the column year then the month number times the rent value
If the expiration year is less then the column year then leave blank

The mix of absolute and relative references will allow for the copiing of the formula

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Michael J. Malinsky wrote: ----

Let's say I have

Monthly Expires 2004 2005 200
2007 2008 Thereafte
Ren
500 12/31/2004 80,496 80,49
200 10/31/2005 2,400 2,00

I'm trying to create a formula that will automatically calculate the tota
payments for each of the next five years and thereafter. So, for example,
formula that would know to calculate 12 months of rent for 2004 and 1
months for 2005 for the 200/month item. I've tried various things to n
avail

TI
 
Seems to do the trick...thanks!

--
Michael J. Malinsky


Mark Graesser said:
Hi Michael,

The following formula assumes your headers are in row 1, and your first
Click to expand...
rent value is in A2. If so then enter this formula in C2 and copy down and
over:
 
Hi Again
I forgot the formula for "Thereafter"

=IF(YEAR($B2)>G$1,DATEDIF(DATE($G$1,12,15),$B2,"m")*$A2,""

This would be entered in H

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Michael J. Malinsky wrote: ----

Let's say I have

Monthly Expires 2004 2005 200
2007 2008 Thereafte
Ren
500 12/31/2004 80,496 80,49
200 10/31/2005 2,400 2,00

I'm trying to create a formula that will automatically calculate the tota
payments for each of the next five years and thereafter. So, for example,
formula that would know to calculate 12 months of rent for 2004 and 1
months for 2005 for the 200/month item. I've tried various things to n
avail

TI
 
Back
Top