Does this formula in U2 copied down work:
=(1 + H2) ^ IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),DATEDIF(F2,TODAY(), "Y"),1) * IF(ISERROR(LOOKUP(2,1/(J2:OFFSET(U2,0,-4)<>""))),G2,I2)
It requires that there are always 3 columns prior to the cell where the calculation is performed for Terms/renewal/Expiration. It also assumes that if ANY amendment has been entered in a row that you use the present rent for the purpose of escalating to the new rent.
[EDIT- I MODIFIED THE ABOVE FORMULA SINCE I FIRST POSTED IT. THE ABOVE IS THE EDITED EFFORT.]
Bookmarks