Sep 21 2020 09:28 PM
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
Sep 21 2020 10:02 PM
@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.
Sep 21 2020 11:10 PM
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
Sep 22 2020 12:37 AM
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.