1

=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

  • IF(AND(F2-E2<0,F2<E2), ... can be simplified to IF(F2<E2, ... since if the subtrahend is larger than the minuend then the difference will always be less than zero. – Jeeped Sep 21 '19 at 3:59
  • Thanks, the equation I had problem with kinda resolved few mins after I posted it, but now I added one more logic to it( I have edited the question). =IF(AND((F2-E2)>0,E2=0),"Extra Item","Extra Qty") and i am again getting the too many argument error again. – Johney Xavier Sep 21 '19 at 4:13
  • See if any of the formulas I've added below shed some light. If you need additional help, edit your question to include it or ask a new question. – Jeeped Sep 21 '19 at 4:18
1

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.

0

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")

  1. The AND((F2-E2)>0,E2=0) is what is being tested.
  2. "Extra Item" is what you get if it is TRUE.
  3. "Extra Qty" is what you get if it is FALSE.

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.