Calculate Value based on date value within a range

[Thomas Kuriakose]

Respected Sirs,

We have a rate to be calculated based on the entered data and this date needs to be within a range of date to pickup the corresponding rate.

1. If Date = 01.01.2016 to 31.12.2016 = 1
2. If Date = 01.01.2017 to 31.12.2017 = 1.03
3. If Date = 01.01.2018 to 31.12.2018 = 1.06
4. If Date = 01.01.2019 to 31.12.9999 = 1.09

If the entered date, e.g.. is 06.06.2015, then this falls between the first range so rate should be 1

Kindly find attached the sheet for your reference.

Thank you very much.

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
Formula in [B3].
Dates and rates in yellow area.
[E4] should to be something like that ( because Your 06.06.2015 sample )

 

[John Jairo V]

Hi @Thomas Kuriakose

Another approach could be:
=LOOKUP(A3,--("01-01-"&{1900;2017;2018;2019}),{1;1.03;1.06;1.09})

Or:
=1+MIN(MAX(,YEAR(A3)-2016)*0.03,0.09)

Blessings!

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for the solutions provided.

Thanks once again,

with regards,
thomas