Multiple Conditions

[Sara_M]

Hi all,
I have an issue coming up with an IF statement with multiple conditions.
Background Info:
We have a agreement from 2023 to 2026 with a company
if the duration is less than 1 year they get cashback of 5% based on Column N on Year 1
If the duration is >= then 1 they get 5% based on column K, for the first year, 4% for the second year and 3% on the third year.
I was able to do this calculation
Fees - First year = IF(O5<1,N5*0.05,K5*0.05)
Fees - Second Year = IF($O3<=1,0,IF($O3>1,$K3*0.04,$N3*0.04))
Fees - Third Year = IF($O3<=2,0,IF($O3>1,$K3*0.03,$N3*0.03))

But my problem is, if the contract started in 2024 instead, the maximum they can reach is 2 years, so I should add a statement saying something like if the year in column B is 2023, then IF($O3<=1,0,IF($O3>1,$K3*0.04,$N3*0.04)), if the year in column B is 2024 then IF(O5<1,N5*0.05,K5*0.05)

Not sure if it's clear, Year 1 depends on when the contract starts.

Many Thanks

 

[Sara_M]

Could you please share a sample screenshot with different dates, different durations and desired results?

Thank you so much. so I am trying to build something dynamic for the next 3 years. so we can add a contract and have the schedule. So this below it would be the outcome if the same contracts started in 2023, or in 2024 or in 2025

 

[Legacy 143009]

Finally sorted Z3:

=IF($O3<1,IF(YEAR($B3)-2022=COLUMN(A1),$N3*0.05,0),IF(AND(YEAR($B3)-2022<=COLUMN(A1),COLUMN(A1)-(YEAR($B3)-2022)<$O3),$K3*((5-(COLUMN(A1)-(YEAR($B3)-2022)))/100),0))

 

[Sara_M]

Finally sorted Z3:

=IF($O3<1,IF(YEAR($B3)-2022=COLUMN(A1),$N3*0.05,0),IF(AND(YEAR($B3)-2022<=COLUMN(A1),COLUMN(A1)-(YEAR($B3)-2022)<$O3),$K3*((5-(COLUMN(A1)-(YEAR($B3)-2022)))/100),0))

Amazing thank you so much, I am going to study properly what you did, it's very cleaver