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
-
And what do you want to happen if A1 does not contain a 0 or a 2? Or if B1 does not contain a 1 or a 2?Gravitate– Gravitate2022-06-06 14:59:34 +00:00Commented Jun 6, 2022 at 14:59
Add a comment
|
3 Answers
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:
3 Comments
Gravitate
Please note that this will be less efficient (although arguably more readable) than just nested
IFs. This won't matter at all unless you are dealing with a very large number of formulas, but just thought it worth mentioning.
Solar Mike
@Gravitate if there are a large number of cells to test, then looping through with vba and pasting the final result would arguably be better, but that was not what the OP asked.
Gravitate
Yes, but using VBA has other issues, such as changing the file format and having to enable content etc. and, as you say, it is not what the OP asked for. I just wanted to point out that it could be made more efficient, if needed, by removing the
ANDs (which do not short circuit), at the expense of some readability.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")
2 Comments
Gravitate
+1 because I agree with you about externalising the conditions, especially when they get more complicated. However the OP dis specifically ask "Is it possible to achieve this with nested IF's in Excel?"
Ike
@Gravitate: sometimes users simply don't ask for that because they don't think about these arguments :-)
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"))
2 Comments
Solar Mike
Have you tested this? I did, and did not get the "invalid input" when a1 was set to 1.It only gives invalid input when both a1 & bb1 are 0, otherwise it gives false.
Gravitate
@SolarMike Apologies. You are correct, I missed an "else". Should be corrected now. Thanks.