int calculation formula error

[shamsu203]

Hi
I am trying to claculate the new rent in row g15,. the following are the name ranger
a DatesFT,c DEbitFT,j timeFT. The formula i used results in 0 amount where as it should be 2121 or the value as on the c column
times the % increase. ie if c4 show 2240 then the amount shoud be 2240*5%. if possible the new date in g16 shoud show 2024 or the date the
increase is due
tks
shamsu

[Glenn Kennedy]

This is either confusing... or a mess... or both.

G12 is a 2021 date. You want to move that forward by a year, to a date that does not exist in column A, and which is also in the past...

Why do you have FUTURE dates in column A?

The 2024 date is 3 years after the date in G12... Why 3?

[shamsu203]

Hi Glen
Sorry the date should have been dec 22. but as the date increase by 12 month each year the amount also will change .what i mean is that when 1 dec 23 is shown in a4 then the new rent in g15 should show the value using c4 as the amount and 5%. the g16 should show the next new date of increase. as the a column gets filled with dates such as 1dec 24 followedd by1 dec 25 then the new rent should be calculated on 1dec24 times5% and the new date 1 dec 25 shown in the date column. I hope iam clear. basically this to alert me when the next increase and amount is due for thiis account

[shamsu203]

Sorry the future date show dates that will occur in the future

[Glenn Kennedy]

Can you upload a sheet that is correct, please?

[shamsu203]

Hi
Attached the file requested
Hope u can help me and the example given is clear Pls bear with me tks
Shamsu

[Glenn Kennedy]

To be honest, I still don't fully get it... but I am able to make a reasonable guess!

=LOOKUP(10^10,C2:C18/(A2:A18=EDATE(G12,G13))*(1+G14))

[JohnTopley]

Why not use a simple compound interest calculation

=$G$11*(1+$G$14)^$K2

where k2 is number of year i.e, 1,2,3

[shamsu203]

hi John
Tks for ur input but that is the formula i am using at the moment. I wanted to avoid using this format as there are similar 10
sheetsand wanted something compact
shamsu

[shamsu203]

Hi Glen
The amount shown for dec 23 is correctly shown as 2277 but when dec 24 is input g 15 does not change to 2338. and as the year changer g 15 should reflect this change. sample file is attached. If it is difficult to achive this I will use the older format I was using erlier
Tks for your patience
Shamsu

[Glenn Kennedy]

It has to pick up the YEAR from somewhere. I used G12. Change it to 1/12/2023 and the value in G15 updates correctly. If It's not coming from G12, where can it come from?

[shamsu203]

Hi Glen
in g 12 is it possible to extract the max date from column A show it in g12(using MAX)
and use that date for the calculation.
tks
Shamsu

[Glenn Kennedy]

Yes... and no.

The problem is that you have not told me WHAT the date in column A actually IS and what the codes in column B MEAN.


Currently, the formula is returning the value from C that corresponds to the "date in G12 plus 1 year" in column A. In the sheet that you last posted, there is already a date of 1/1/24 in column A. It is the maximum.

IF I change the value in G12 to be the maximum in column A... 1/1/24, =MAX(A2:A18) it looks for a date 1 year later, i.e. 1/1/25 and does not find it. So that approach FAILS. Is there another way of telling WHICH value in column A corresponds to the NEW rate for the CURRENT year?

[shamsu203]

Hi Glen
Can u see the attached sheet and see if the formula is correct. g 5 contanins the current date if it will hlep
Shamsu

[Glenn Kennedy]

You seem to have gone back to an old sheet. Pleasde update the one that I last posted.

What on earth is
=G15*(1+G14)^G1312 this bit in red supposed to be doing???

[shamsu203]

Hi Glen
I do not know where the 1312 came from . I attach ur amended sheet.
shamsu