next increase date

[shamsu203]

Hi
I have a work sheet part of which has the following
start date row d18 01-01-17
Increase int k19 36 (months)

next incre due k22 01-01-23

helper table k24 01-01-20
with date going down in increamental of 36 months
At present i am using the if formula to calculate the next increase date

IF(DATEDIF(K31,$B$1,"m")>=K32*$A$36,K50,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$35,K49,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$34,K48,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$33,K47,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$32,K46,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$31,K45,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$30,K44,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$29,K43,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$28,K42,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$27,K41,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$26,K40,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$25,K39,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$24,K38,IF(DATEDIF(K31,$B$1,"m")>=K32*$A$24,K37,K37)))))))))))))) is there a way to reduce this formula Regrds Shamsu

 

[jasonb75]

Maybe something like

=IFERROR(LOOKUP(DATEDIF(K31,$B$1,"m")/K32,$A$24:$A$36,K37:K50),K37)

If you posted your examples using XL2BB it might make it easier for you to understand.

 

[shamsu203]

Hi
Great it works just fine . Was wondering if it is possible to remove the helper column and get the increase date
Tks a lot for ur help much appreciated
Regrds
Shamsu

 

[jasonb75]

You could probably do away with the helper table as well, but the information in your post is not clear enough for me to do that. See the link below for a tool that will help you to provide a clear example of your sheet.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[shamsu203]

starts from row 17 to row 54
column a	column b
Contract Rent a17	₹ 16,500​
Lease Start Date	15-02-19​
Rent Increase Interval	12​
Rent Increase %	10% ​
Current Rent Rate	₹ 18,150​
Next Rent Increase date	15-02-21​
New Rent on increase	₹ 19,965​
1​	15-02-20​
2​	15-02-21​
3​	15-02-22​
4​	15-02-23​
5​	15-02-24​
6​	15-02-25​
7​	15-02-26​
8​	15-02-27​
9​	15-02-28​
10​	15-02-29​
11​	15-02-30​
12​	15-02-31​
13​	15-02-32​
14​	15-02-33​
15​	15-02-34​
16​	15-02-35​
17​	15-02-36​
18​	15-02-37​
19​	15-02-38​
20​	15-02-39​
21​	15-02-40​
22​	15-02-41​
23​	15-02-42​
24​	15-02-43​
25​	15-02-44​
26​	15-02-45​
27​	15-02-46​
28​	15-02-47​
29​	15-02-48​
30​	15-02-49​
31​	15-02-50​
HI I tried to add the download of the add in but could not do so . I am using excel 365. The row starts from a17 and ends at b17. I hope that you will be able to help me with the information provided

 

[jasonb75]

It's not so easy to follow a copy and paste, the add-in keeps the formatting and formulas from the source so it gives us a lot more information to work with.
I think that this gives you what you need.

Book1
	A	B
16	column a	column b
17	Contract Rent a17	₹ 16,500.00
18	Lease Start Date	15-02-02
19	Rent Increase Interval	12
20	Rent Increase %	10%
21	Current Rent Rate	₹ 18,150.00
22	Next Rent Increase date	15-02-02
23	New Rent on increase	₹ 19,965.00
Sheet1

Cell Formulas
Range	Formula
B21	B21	=B23/(1+B20)
B22	B22	=EDATE(B18,CEILING(DATEDIF(B18,TODAY(),"m"),12))
B23	B23	=B17*(1+B20)^DATEDIF(B18,B22,"y")

 

[shamsu203]

wow tks a million works perfectly . First time seen the ceiling function .

 

[shamsu203]

ONe QUICK QUESTION if the rent increase interval changes does it effect the calculations. this is what i am finding now after copying to other columns

 

[jasonb75]

Sorry, that was my error. I had set the 'New rent' formula to yearly increase instead of the correct interval. This formula corrects that

=B17*(1+B20)^(DATEDIF(B18,B22,"m")/B19)

 

[shamsu203]

tks that works fine tks again for your help