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

 

[Legacy 143009]

Like this?

=IF(YEAR(B5)>=2024,IF(O5<1,N5*0.05,K5*0.05),IF($O3<=1,0,IF($O3>1,$K3*0.04,$N3*0.04)))

 

[Sara_M]

Like this?

=IF(YEAR(B5)>=2024,IF(O5<1,N5*0.05,K5*0.05),IF($O3<=1,0,IF($O3>1,$K3*0.04,$N3*0.04)))[/CODE
[/QUOTE]
I think this is resolving the issue, thank you

 

[Legacy 143009]

You are welcome

 

[Sara_M]

Sorry, I just tested, this fixes the charges for year 2, but on the third column I still need to show the 5% 4% and 3% options depending if the contract started in 2023, 2024 or 2025. How can i combine them?

 

[Legacy 143009]

LOOKUP function will be more practical if you have several conditions:

=LOOKUP(YEAR(B5), {2023;2024;2025}, {IF($O3<=1,0,IF($O3>1,$K3*0.04,$N3*0.04));IF(O5<1,N5*0.05,K5*0.05);IF("something for 2025")})

Or you can assign percentages directly if it fits for you:

=LOOKUP(YEAR(B5), {2023;2024;2025}, {0.05;0.04;0.03})

 

[Sara_M]

LOOKUP(YEAR(B5), {2023;2024;2025}, {IF($O3<=1,0,IF($O3>1,$K3*0.04,$N3*0.04));IF(O5<1,N5*0.05,K5*0.05);IF("something for 2025")})

Sorry, never used this formula, what would i add in "something for 2025" if i had to add in on this line for example?

At the moment i have in:
Fees for Year one IF($O4<=1,$N4*0.05,$K4*0.05)
Fees for Year Two (your formula fixed the issue wonderfully) IF(YEAR($B4)>=2024,IF($O4<1,$N4*0.05,$K4*0.05),IF($O4<=1,0,IF($O4>1,$K4*0.04,$N4*0.04)))
Fees for Year three I have this at the moment but only works in the contract starts in 2023 IF($O4<=2,0,IF($O4>1,$K4*0.03,$N4*0.03)).

 

[Legacy 143009]

Assuming Duration column is G, paste this to First Fee Column:

=IF(COLUMN(A1)>$G4,"",LOOKUP(COLUMN(A1), {1;2;3}, {IF($O4<=1,$N4*0.05,$K4*0.05);IF($O4<=1,0,IF($O4>1,$K4*0.04,$N4*0.04));IF($O4<=2,0,IF($O4>1,$K4*0.03,$N4*0.03))})

I am afraid this is the best I can offer. maybe you should wait for other answers.

 

[Sara_M]

=IF(COLUMN(A1)>$G4,"",LOOKUP(COLUMN(A1), {1;2;3}, {IF($O4<=1,$N4*0.05,$K4*0.05);IF($O4<=1,0,IF($O4>1,$K4*0.04,$N4*0.04));IF($O4<=2,0,IF($O4>1,$K4*0.03,$N4*0.03))})

it's giving me an error

 

[Legacy 143009]

OK,

I rewrite the formula according to your needs (I guess). I think years are irrelevant because you already have your duration in column O. It is still uncertain for me that when to house column K and column N. Paste this formula to Z3 and pull to right and down.

=IF($O3<1,$N3*0.05,IF($O3<COLUMN(A1),0,$K3*((6-COLUMN(A1))/100)))