Kennethy86
New Member
- Joined
- Jul 27, 2018
- Messages
- 3
SUMIFS(input_table[Quoted Rent],input_table[GO Date],"<="&'current&future_streams_combined'!B$75,input_table[Licencee],'current&future_streams_combined'!$A76,input_table[City],"<>NEW")
I'm currently using this formula to lookup and sum the rents by licensee from my data table where the date on my formula page is greater than the go date on the input table, which is when rents will be due. Any idea how I could build in an escalation of rent at 2 percent compounded annually?
[TABLE="width: 1001"]
<colgroup><col><col span="14"></colgroup><tbody>[TR]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]Jul-19[/TD]
[TD="align: right"]Aug-19[/TD]
[TD="align: right"]Sep-19[/TD]
[TD="align: right"]Oct-19[/TD]
[TD="align: right"]Nov-19[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,075[/TD]
[TD] 4,075[/TD]
[TD] 4,075[/TD]
[TD] 4,075[/TD]
[TD] 8,355[/TD]
[TD] 8,355[/TD]
[TD] 8,355[/TD]
[TD] 8,355[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 8,150[/TD]
[TD] 8,150[/TD]
[TD] 8,150[/TD]
[TD] 20,267[/TD]
[TD] 20,267[/TD]
[TD] 24,807[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,050[/TD]
[TD] 4,050[/TD]
[TD] 16,720[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[/TR]
[TR]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[/TR]
[TR]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 57,765[/TD]
[TD] 57,765[/TD]
[TD] 57,765[/TD]
[TD] 73,882[/TD]
[TD] 82,007[/TD]
[TD] 86,547[/TD]
[TD] 107,617[/TD]
[TD] 112,447[/TD]
[TD] 116,727[/TD]
[TD] 116,727[/TD]
[TD] 116,727[/TD]
[TD] 116,727[/TD]
[/TR]
</tbody>[/TABLE]
I'm currently using this formula to lookup and sum the rents by licensee from my data table where the date on my formula page is greater than the go date on the input table, which is when rents will be due. Any idea how I could build in an escalation of rent at 2 percent compounded annually?
[TABLE="width: 1001"]
<colgroup><col><col span="14"></colgroup><tbody>[TR]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]Jul-19[/TD]
[TD="align: right"]Aug-19[/TD]
[TD="align: right"]Sep-19[/TD]
[TD="align: right"]Oct-19[/TD]
[TD="align: right"]Nov-19[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[TD] 4,035[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[TD] 4,280[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,075[/TD]
[TD] 4,075[/TD]
[TD] 4,075[/TD]
[TD] 4,075[/TD]
[TD] 8,355[/TD]
[TD] 8,355[/TD]
[TD] 8,355[/TD]
[TD] 8,355[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 8,150[/TD]
[TD] 8,150[/TD]
[TD] 8,150[/TD]
[TD] 20,267[/TD]
[TD] 20,267[/TD]
[TD] 24,807[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[TD] 33,207[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,050[/TD]
[TD] 4,050[/TD]
[TD] 16,720[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[TD] 21,550[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[TD] 3,500[/TD]
[/TR]
[TR]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[/TR]
[TR]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[TD] 4,000[/TD]
[/TR]
[TR]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 37,800[/TD]
[TD] 57,765[/TD]
[TD] 57,765[/TD]
[TD] 57,765[/TD]
[TD] 73,882[/TD]
[TD] 82,007[/TD]
[TD] 86,547[/TD]
[TD] 107,617[/TD]
[TD] 112,447[/TD]
[TD] 116,727[/TD]
[TD] 116,727[/TD]
[TD] 116,727[/TD]
[TD] 116,727[/TD]
[/TR]
</tbody>[/TABLE]