Forum Discussion

donny475's avatar
donny475
Copper Contributor
Sep 22, 2020

If formual

HI 

 

I need to set up an if function to compare text results in my spreadsheet eg. if cell 80 has a P and cell 79 has a b than the result should be 1 , if Cell 80 has a P and cell 79 has  a P then result should be -1

can someone help with this 

excel

3 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Sep 22, 2020

    donny475 Try something like this:

    =IFERROR(IFS(AND(A79="b",A80="p"),1,AND(A79="p",A80="p"),-1),"")

    Note: the search strings "b" and "p" are not case-sensitive. 

    • donny475's avatar
      donny475
      Copper Contributor
      Sep 22, 2020

      Thanks for that it worked but I also with in the same formulae need the reverse ie if a80 =b and a79 =P 1

      and if A80 =b and a79 =b -1 

       

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Sep 22, 2020

        donny475 

        Just add the extra conditions like this:

        =IFERROR(IFS(AND(A79="b",A80="p"),1,AND(A79="p",A80="p"),-1,AND(A79="p",A80="b"),1,AND(A79="b",A80="b"),-1),"")

         Or, if you intend to add a loot more combinations, consider using a lookup table. Then, the formula will become much shorter and easier to maintain. See the workbook attached for an example.

         

Resources