vendredi 3 juillet 2020

IF Statement using IF, OR, AND with multiple logics ERROR

I am trying to write a formula based on the following conditions and it is not working

  • IF A1 = "S", and B1 = 254 or 127, Use LIST NOLIST
  • IF A1 = "F" or "M", and B1 = 254, and C1 is even, Use LIST EVEN254
  • IF A1 = "F" or "M", and B1 = 254, and C1 is odd, use LIST ODD254
  • IF A1 = "F" or "M", and B1 = 127, and C1 is even, Use LIST EVEN127
  • IF A1 = "F" or "M", and B1 = 254, and C1 is odd, use LIST ODD127

Here's the formula I am currently using. I am receiving an #VALUE error

=IF(OR(AND(B1=127,B1=254),AND(A1="S")),NOLIST,IF(OR(AND(A1="F",A1="M"),AND(B1=127,C1="T")),EVEN127,IF(OR(AND(A1="F",A1="M"),AND(B1=127,C1="F")),ODD127,IF(OR(AND(A1="F",A1="M"),AND(B1=254,C1="T")),EVEN254,IF(OR(AND(A1="F",A1="M"),AND(B1=254,C1="F")),ODD254,"")))))

I also tried this which did not work either

=IF(AND(B1=254,C1="T",OR(A1="F",A1="M")),EVEN254,IF(AND(B1=254,C1="F",OR(A1="F",A1="M")),ODD254,IF(AND(B1=127,C1="T",OR(A1="F",A1="M")),EVEC127,IF(AND(B1=127,C1="F",OR(A1="F",A1="M")),ODD127,IF(AND(B1=254,B1=127),OR(A1="S")),NOLIST,""))))

My initial formula was working, and then I realized I had to add the conditions for the ODD/EVEN numbers so after I added those conditions to the formula, it stopped working. Here's my original formula

=IF(OR(AND(B1=127,B1=254),AND(A1="S")),NOLIST,IF(OR(AND(A1="F",A1="M"),AND(B1=127)),EVEN127,IF(OR(AND(A1="F",A1="M"),AND(B1=254)),EVEN254,"")))

I changed the list names for simplicity, so the original list names did not contain even/odd.

Can anyone help me solve this?

Aucun commentaire:

Enregistrer un commentaire