Using the IF formula show commission based on what year customer was created

[tonycel]

I am looking to show a percentage based on the year. The 1st year I get 4% of column E. The 2nd year I get 1.5%, the 3rd year I get 1%, the 4th year I get 0.5% and the 5th year on I do not get a commission. Can you please show how to write this formula.

[etaf]

Is this based on the creation date
Then you could use
=YEARFRAC(I2,TODAY()) to calculate the year fraction

Then use INT() to just get the complete years
=INT(YEARFRAC(I2,TODAY()))

now you can use an nested IF to work out the commision

=IF(INT(YEARFRAC(I2,TODAY())) >= 4, (E2*0.04)+(E2*0.015)+(E2*0.01)+(E2*0.005),IF(INT(YEARFRAC(I2,TODAY())) = 3, (E2*0.04)+(E2*0.015)+(E2*0.01),IF(INT(YEARFRAC(I2,TODAY())) = 2, (E2*0.04)+(E2*0.015),IF(INT(YEARFRAC(I2,TODAY())) = 1, (E2*0.04), 0))))

[tonycel]

Your formula is great, but the only issue is rows 4, 5, 6 the create date is 2012. I only get commissions based on 5 years where I would not get a commission for these dates. Is there a way to edit the formula to show 1st yr 4%, 2nd yr 1.5% 3rd yr 1% 4th yr 0.5% and 5th yr, 6th, 7th, 8th, 9th yrs show zero commission.

[etaf]

OK, so we could just add another IF for 5 or greater as zero, and then usd = 4 only

=IF(INT(YEARFRAC(I2,TODAY())) >= 5, 0 , IF(INT(YEARFRAC(I2,TODAY())) = 4, (E2*0.04)+(E2*0.015)+(E2*0.01)+(E2*0.005),IF(INT(YEARFRAC(I2,TODAY())) = 3, (E2*0.04)+(E2*0.015)+(E2*0.01),IF(INT(YEARFRAC(I2,TODAY())) = 2, (E2*0.04)+(E2*0.015),IF(INT(YEARFRAC(I2,TODAY())) = 1, (E2*0.04), 0)))))

usually I would change so we don't have zero at beginning and end

But....
does that work OK

[tonycel]

Maybe I am not explaining it correctly. If I bring on a new customer I get 4% commission based on the start date, counting backwards or from 8/4/2020 and 8/4/2019 I get 4% commission, from 8/3/2019 to 8/4/2018 I get 1.5% commission. From 8/3/2018 to 8/4/2017 I get 1% commission. from 8/3/2017 to 8/4/2016 I get 0.5% commission. From 8/3/2016 to 1/1/2000 I get zero commission.

Row 9 - I should get $64.00 commission but the formula says I get $0.00 -

[bebo021999]

How about:

Please Login or Register  to view this content.