CODEWITHSUNDEEP
excelif-statement
Zeeshan Shahid
Zeeshan Shahid

Reputation: 1

too many arguments for this formula

I am making excel sheet but excel is not allowing me further checks and giving an error to many argument for this formula.

kindly give me solution.

=IF(E2="NL",N2-(J2*0.08),IF(AND(E2="PA",H2="Trip Travel"),(N2*0.01)+N2,(N2*0.02)+N2,IF(AND(E2="PK",H2="Trip Travel"),(J2*0.01)+N2,(J2*0.02)+N2)))

Edit to clarify possible scenarios:

 1. E is "NL" and H is "Other"       : return N2+(J2*0.08)
 2. E is "NL" and H is "Trip Travel" : return N2+(J2*0.08)
 3. E is "PK" and H is "Other"       : return N2+(J2*0.01)
 4. E is "PK" and H is "Trip Travel" : return N2+(J2*0.01)
 5. E is "PA" and H is "Other"       : return N2+(N2*0.02)
 6. E is "PA" and H is "Trip Travel" : return N2+(N2*0.01)

Upvotes: 0

Views: 79

Answers (1)

elmer007
elmer007

Reputation: 1445

The problem is the 2nd IF function, which has 4 arguments given. IF only takes 3:

  1. The condition to test
  2. The result if the condition is true
  3. The result if the condition is false

The problematic section is:

IF(AND(E2="PA",H2="Trip Travel"), (N2*0.01)+N2, (N2*0.02)+N2, IF(AND(E2="PK",H2="Trip Travel"),(J2*0.01)+N2,(J2*0.02)+N2))

Notice how there are 4 arguments for the IF function in that section:

  1. The condition: AND(E2="PA",H2="Trip Travel")
  2. The result if true: (N2*0.01)+N2
  3. The result if false: (N2*0.02)+N2
  4. And then this extra argument: IF(AND(E2="PK",H2="Trip Travel"),(J2*0.01)+N2,(J2*0.02)+N2)

I'm not sure how exactly to correct this since I can't determine the logic you want from that formula. However, I'm guessing that either (N2*0.01)+N2 or (N2*0.02)+N2 belongs in a different section.

Also, just to help simplify the formula, the "+N2" could be put before the second IF and remove all the "+N2"s since all outcomes have N2 added to them. Something like the following (which still has the 4 arguments since I'm not sure how you want the logic corrected):

=IF(E2="NL",N2-(J2*0.08), N2+IF(AND(E2="PA",H2="Trip Travel"),(N2*0.01),(N2*0.02),IF(AND(E2="PK",H2="Trip Travel"),(J2*0.01),(J2*0.02))))

Edit the edited question:

This formula should cover the six possibilities in your spreadsheet:

=IFERROR(N2+IF(E2="NL",J2*0.08,IF(E2="PK",J2*0.01,IF(E2="PA",IF(H2="Trip Travel",N2*0.01,IF(H2="Other",N2*0.02,"n/a")),"n/a"))),"n/a")

Upvotes: 2

Related Questions