Excel Formula

Question

Hi all,
I am new to this community and hope I'm in the right place to get an answer to my question.

It is as follows;

I need to find a formula to accomplish task in Excel.

I am working with cells A1, B1 and C1.

If cells A1 and B1 contain the text "yes" I want to see the number 0 in cell C1,

but if cells A1 and B1 contain the text "No" I also want to see the number 0 in C1

and if cell A1 contains the text "yes" but cell B1 contains the text "no" I want to see 100 in C1 and if cell A1 contains the text "no" but cell B1 contains the text "yes" I want to see -100 in cell C1.

Excel is so amazing that I have to think there is a formula for this.

Please help!

Thanks in advance,

Schrambo4430

Answer 1

@James Schram

You may combine IFs and Ands to create a composite condition. Try the following formula and see whether it works.

=IF(OR(AND(A1="Yes",B1="Yes"), AND(A1="No", B1="No")), 0, IF(AND(A1="Yes",B1="No"), 100,IF(AND(A1="No",B1="Yes"),-101,"Error")))  

See the results

Some references
https://support.microsoft.com/en-us/office/use-nested-functions-in-a-formula-9d7c966d-6030-4cd6-a052-478d7d844166
https://www.ablebits.com/office-addins-blog/if-and-formula-in-excel/

Hope this helps

Answer 2

If you have lots of cases (if) then better is add next table with conditions.
Then you do not use if formula, but vlookup

Regards.

Answer 3

I was thinking that a combination of "if", "or" and "and" functions would produce a formula that even a novice would understand.
Someone, sreejukg, created a formula for me and it it makes sense to me....AND WORKS!!!! Thanks to Sreejukg!!!
Also, thank you for getting back to me. I'd also be interested to see if anyone else came up with the formula.
Schrambo4430