Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,


    I have a query regarding the below mentioned formula. I am using several or and if conditions just on two columns ie.e column G and Column M.

    =IF(OR(G2="A999",G2="B999",G2="C999",G2="999A",G2= "999B",G2="999C")*(M2="Yes"),A2&" "&"AUTO"&" "&"INCORRECT",IF(OR(G2="A999",G2="B999",G2="C999", G2="999A",G2="999B",G2="999C")*(M2="No"),A2&" "&"AUTO"&" "&"CORRECT",IF(OR(G2="1 India",G2="2 India",G2="India 1",G2="India 2",G2="USA")*(M2="Yes"),A2&" "&"MANUAL"&" "&"INCORRECT",IF(OR(G2="1 India",G2="2 India",G2="India 1",G2="India 2",G2="USA")*(M2="No"),A2&" "&"MANUAL"&" "&"CORRECT"))))

    Now, I want to use if condition for Column A as well such that when there is a value "ABC" in column A,it should return "ABC" in place of A2 in the formula but if there is any other value other than "ABC", formula should replace A2 value in the formula with "Others".

    PLEASE CONSIDER THAT I AM USING EXCEL 2003

    Can anyone help me on this.....




    Thanks,
    Ankit

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try:

    =IF(OR(G2="A999",G2="B999",G2="C999",G2="999A",G2= "999B",G2="999C")*(M2="Yes"),IF(A2="ABC",A2,"Other s")&" "&"AUTO"&" "&"INCORRECT",IF(OR(G2="A999",G2="B999",G2="C999", G2="999A",G2="999B",G2="999C")*(M2="No"),IF(A2="AB C",A2,"Others")&" "&"AUTO"&" "&"CORRECT",IF(OR(G2="1 India",G2="2 India",G2="India 1",G2="India 2",G2="USA")*(M2="Yes"),IF(A2="ABC",A2,"Others" )&" "&"MANUAL"&" "&"INCORRECT",IF(OR(G2="1 India",G2="2 India",G2="India 1",G2="India 2",G2="USA")*(M2="No"),IF(A2="ABC",A2,"Others") &" "&"MANUAL"&" "&"CORRECT"))))

    Note that the 4 instances of A2 have been replaced with IF(A2="ABC",A2,"Others")
    Regards
    Don

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Here is a shorter version that will do the same job

    =IF(A2="ABC","ABC ","Others ")&IF(OR(G2="A999",G2="B999",G2="C999",G2="999A",G 2="999B",G2="999C"),"AUTO ",IF(OR(G2="1 India",G2="2 India",G2="India 1",G2="India 2",G2="USA"),"MANUAL "))&IF(M2="No","CORRECT",IF(M2="Yes","INCORREC T"))
    Regards
    Don

  4. #4
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks a ton... It works....

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    =IF(A2="ABC","ABC ","Others ")&IF(OR(G2={"A999","B999","C999","999A","999B","9 99C"}),"AUTO ",IF(OR(G2={"1 India","2 India","India 1","India 2","USA"}),"MANUAL ",""))&IF(M2="No","CORRECT",IF(M2="Yes","INCORRECT "))

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by bosco_yip View Post
    =IF(A2="ABC","ABC ","Others ")&IF(OR(G2={"A999","B999","C999","999A","999B","9 99C"}),"AUTO ",IF(OR(G2={"1 India","2 India","India 1","India 2","USA"}),"MANUAL ",""))&IF(M2="No","CORRECT",IF(M2="Yes","INCORRECT "))
    Thanks for that improvement.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •