If formual

%3CLINGO-SUB%20id%3D%22lingo-sub-1697709%22%20slang%3D%22en-US%22%3EIf%20formual%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1697709%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20set%20up%20an%20if%20function%20to%20compare%20text%20results%20in%20my%20spreadsheet%20eg.%20if%20cell%2080%20has%20a%20P%20and%20cell%2079%20has%20a%20b%20than%20the%20result%20should%20be%201%20%2C%20if%20Cell%2080%20has%20a%20P%20and%20cell%2079%20has%26nbsp%3B%20a%20P%20then%20result%20should%20be%20-1%3C%2FP%3E%3CP%3Ecan%20someone%20help%20with%20this%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1697709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1698346%22%20slang%3D%22en-US%22%3ERe%3A%20If%20formual%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F802464%22%20target%3D%22_blank%22%3E%40donny475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20add%20the%20extra%20conditions%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(IFS(AND(A79%3D%22b%22%2CA80%3D%22p%22)%2C1%2CAND(A79%3D%22p%22%2CA80%3D%22p%22)%2C-1%2CAND(A79%3D%22p%22%2CA80%3D%22b%22)%2C1%2CAND(A79%3D%22b%22%2CA80%3D%22b%22)%2C-1)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BOr%2C%20if%20you%20intend%20to%20add%20a%20loot%20more%20combinations%2C%20consider%20using%20a%20lookup%20table.%20Then%2C%20the%20formula%20will%20become%20much%20shorter%20and%20easier%20to%20maintain.%20See%20the%20workbook%20attached%20for%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1698264%22%20slang%3D%22en-US%22%3ERe%3A%20If%20formual%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698264%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20that%20it%20worked%20but%20I%20also%20with%20in%20the%20same%20formulae%20need%20the%20reverse%20ie%20if%20a80%20%3Db%20and%20a79%20%3DP%201%3C%2FP%3E%3CP%3Eand%20if%20A80%20%3Db%20and%20a79%20%3Db%20-1%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1698188%22%20slang%3D%22en-US%22%3ERe%3A%20If%20formual%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698188%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F802464%22%20target%3D%22_blank%22%3E%40donny475%3C%2FA%3E%26nbsp%3BTry%20something%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(IFS(AND(A79%3D%22b%22%2CA80%3D%22p%22)%2C1%2CAND(A79%3D%22p%22%2CA80%3D%22p%22)%2C-1)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ENote%3A%20the%20search%20strings%20%22b%22%20and%20%22p%22%20are%20not%20case-sensitive.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 

3 Replies
Highlighted

@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. 

Highlighted

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 

 

 

Highlighted

@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.