Hi,
I am wondering if there is a way to simplify this formula (below). This formula basically spits out the difference in rent within a given time period (months). I would like to simplify it so if i need to add more month ranges I dont have to keep going into the formula and adding the criteria from that row. Is there a way to use a lookup function to do this? Maybe you look up the month in row 73 in the table in columns C & D? If i am not explaining it well just comment. The below formula is for the "Rent Escalations (Custom)" row. If you need clarification or better data please let me know.
I am wondering if there is a way to simplify this formula (below). This formula basically spits out the difference in rent within a given time period (months). I would like to simplify it so if i need to add more month ranges I dont have to keep going into the formula and adding the criteria from that row. Is there a way to use a lookup function to do this? Maybe you look up the month in row 73 in the table in columns C & D? If i am not explaining it well just comment. The below formula is for the "Rent Escalations (Custom)" row. If you need clarification or better data please let me know.
Excel Formula:
=+IF(F73="","",IF($E$14="Custom",IFS(F73=0,0,AND(F73>=$C$16,F73<=$D$16),$F$16-$E$13,AND(F73>=$C$17,F73<=$D$17),$F$17-$E$13,AND(F73>=$C$18,F73<=$D$18),$F$18-$E$13,AND(F73>=$C$19,F73<=$D$19),$F$19-$E$13,AND(F73>=$C$20,F73<=$D$20),$F$20-$E$13,AND(F73>=$C$21,F73<=$D$21),$F$21-$E$13,AND(F73>=$C$22,F73<=$D$22),$F$22-$E$13,AND(F73>=$C$23,F73<=$D$23),$F$23-$E$13,AND(F73>=$C$24,F73<=$D$24),$F$24-$E$13,AND(F73>=$C$25,F73<=$D$25),$F$25-$E$13),""))
