Using IF, AND, OR conditional formula

[Fareed]

Hello Chandoo,


I am a newbie here , came up with a problem with IF, AND, OR conditions. I have my columns like this:


Sno,Empname,Dept,Rating,DOJ,OldSalary,Increment,Bonus,New Salary.


To get a bonus of 40,000/-, he should be either from Sales dept OR Marketing dept AND his DOJ should be more than 7years till today + Rating should be more than 8pts.


I tried to construct IF,AND,OR's following combinations like this:

1. IF(OR(C2="Sales",C2="Marketing"),IF(AND(TODAY()-E2/365.25>=7,D2>=8),40000,0))

2. IF(AND(TODAY()-E2/365.25>=7,D2>=8),IF(OR(C2="Sales",C2="Marketing"),40000,0))


like this but its wrong and its not giving the required results properly as you can see. I think I have explained clearly but if you still need the download link, I can give post you later. Thank you.


Fareed Ahmed

 

[NARAYANK991]

Hi Fareed ,


Two of your conditions are correct :


1. OR(C2="Sales",C2="Marketing")


2. D2>=8


In (2) , you have to decide whether D2>8 is correct or D2>= 8 is correct.


The third condition can be specified as :


3. DATE(YEAR(E2)+7,MONTH(E2),DAY(E2))<=TODAY()


where E2 contains the DOJ.


The final IF statement should have an AND of all the three conditions , so your formula would be :


=IF(AND(OR(C2="Sales",C2="Marketing"),DATE(YEAR(E2)+7,MONTH(E2),DAY(E2))<=TODAY(),D2>=8),40000,0)


Narayan

 

[Fareed]

Wow..Its working fine.. Thanks alott Narayan.