I am trying to write an Excel function in order to compare the values of two columns and write in a third column a specific value, dependent of that comparison. The conditions that need to be simultaneously met are the following:
IF A1 = 0 AND B1 = 1 THEN C1 = 2 IF A1 = 0 AND B1 = 2 THEN C1 = 1 IF A1 = 2 AND B1 = 1 THEN C1 = 3 IF A1 = 2 AND B1 = 2 THEN C1 = 4
Is it possible to achieve this with nested IF's in Excel?
Many thanks
CodePudding user response：
So, based on what you state, this:
=if(and(a1=0,b1=1),2,if(and(a1=0,b1=2),1,if(and(a1=2,b1=1),3,if(and(a1=2,b1=2),4,"check"))))
Proving most cases:
CodePudding user response：
What I would do, is to externalize those conditions. It helps you (and everyone else) whenever
- your boss wants to know how your are calculating this specific value --> you simply show her the condition table (no need to look into the formula)
- your boss (or the data itself) wants you to add another condition --> you simply add a new row to the conditions table (no need to make the formula longer)
- ...
The formula I use in Column C of the data table:
=IFERROR(FILTER(tblConditionC[C],(tblConditionC[A]=[@A]) *(tblConditionC[B]=[@B])),"no mapping")
CodePudding user response：
This should do what you want:
=IF(A1=0,IF(B1=1,2,IF(B1=2,1,"Invalid Input")),IF(A1=2,IF(B1=1,3,IF(B1=2,4,"Invalid Input")),"Invalid Input"))