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]

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)))

I will try thanks, but the years are important, if the start of the contact is in 2024, the first year will be in column AA (5%) and the second in column AB (4%), but if the contract starts in 2023, then the First year will be in column Z (5%), the second in column AA(4%) and the third year in column AB (3%)

 

[Legacy 143009]

if the start of the contact is in 2024, first year will be in column AA (5%)

ahh ok.. I think this must work..

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

 

[Legacy 143009]

I think this is what you want:

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

 

[Sara_M]

I think this is what you want:

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

Hi, what do you have in column A1?
So sorry for the many many questions, I am very grateful of all your help

 

[Legacy 143009]

Oh, nothing, I am just using it to count like 1,2,3...
So I know If I am on Z or AA or AB.

 

[Sara_M]

Oh, nothing, I am just using it to count like 1,2,3...
So I know If I am on Z or AA or AB.

I am very sorry, you have me a little confused, so I am aiming to a blank cell

 

[Legacy 143009]

Yes,
But its purpose is not to pull a blank value.
COLUMN (A1) will return 1.
When you pull to right, COLUMN(B1) will return 2.

So I will know my position through Z to AB.

 

[Sara_M]

Yes,
But its purpose is not to pull a blank value.
COLUMN (A1) will return 1.
When you pull to right, COLUMN(B1) will return 2.

So I will know my position through Z to AB.

I see, thanks, but in this way I am getting the same number of % in year 1 2 and 3 even if it's <1 year of contract. This excel seems to be a bit of a challenge

 

[Legacy 143009]

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

 

[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