=IF((F2-E2)=0,"No mismatch",IF(AND((F2-E2)>0,E2=0),"Extra Item","Extra Qty"),IF(AND((F2-E2)<0,F2>0),"Short Qty","Not received"))
It gives me an error of too many arguments
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
You're missing a closing bracket at the end of the formula because you have an extra closing bracket in the
AND(...), ...) clause.
'fixed =IF((F2-E2)=0,"No mismatch",IF((F2-E2)>0,"Extra Qty",IF(AND(F2-E2<0,F2<E2),"Short Qty","Not received"))) 'improved =IF((F2-E2)=0,"No mismatch",IF((F2-E2)>0,"Extra Qty",IF(F2<E2,"Short Qty","Not received"))) 'check for blanks added =IF(COUNT(E2:F2)<>2,"Not received",IF(F2-E2=0,"No mismatch",IF(F2-E2>0,"Extra Qty","Short Qty"))) 'alternate =IF(COUNT(E2:F2)<>2,"Not received", CHOOSE(SIGN(F2-E2)+2,"Short Qty","No mismatch", "Extra Qty"))
Minimize the use of brackets to what is absolutely necessary.
In addition to any other concerns, you are not nesting the `IF()' properly. It seems to be a conceptual problem, easily remedied though.
For instance, the first
IF() follows the traditional nested
IF()'s form: IF this, then THAT, ELSE this new IF. All good. First argument tests something, second is what you want if it is TRUE, third argument is the next IF.
The very next
IF() though, the first one with an
AND() is "complete": it has all three arguments used in it and thereby ends your formula:
IF(AND((F2-E2)>0,E2=0),"Extra Item","Extra Qty")
There is nowhere for the next
IF() to be considered. The formula stops here but you typed more so it tells you there is too much. The actual form of things makes the next
IF() a fourth argument, and the last
IF() a fifth argument so it gives the "too many arguments" message.
One guess as to how it may have happened is you could be using a formula "template" from somewhere and are supposed to replace those results that are in quotes with whatever you like best for the first of each pair, then replace the second thing that is in quotes with the whole next
IF() clause but instead changed the first leaving the second intact. But that's just a (poor?) guess.
AND() clauses are perfectly fine. You have NOT left out any parentheses or misplaced them in those clauses as I see them — although the question has been edited and perhaps that was originally so but is fixed now (though it does not look like that is so when I try to make out the edit). You COULD drop the ()'s around the "F2-E2" parts, but you might feel those make it more readable to you. Or your boss perhaps. Or, since formulas can occasionally get really different if parentheses are left out, someone may have taught you to use them more than strictly necessary. (Weird = sometimes a function might work with a range in parentheses, but fail without them, for no obvious reason.) Absolutely nothing you are doing here makes them necessary if you want to remove them as others push for.