0

This formula: 

=IF(AND(A2<H2),(A2>=0),G2,IF(AND(A2<H3,A2>=H2),G3),(IF(AND(A2<H4,A2>=H3),G4),(IF(AND(A2<H5,A2>=H4),G5),IF(AND(A2<H6,A2>=H5),G6)

gives me the error, "You've entered too many arguments for this function".

I have no false statements; I just want them to execute when they are true.

Improve this question
2
  • The question is about the syntax of the IF statement, but it actually looks as if you're trying to use the list of values in column H to decide which value to assign from column G. In that case it would be easier to use vlookup. Commented Oct 8, 2014 at 13:58
  • if you have multiple IF() statements consider using a VLOOKUP() instead Commented Oct 9, 2014 at 10:13

2 Answers 2

Reset to default
2

You can troubleshoot a formula by simply looking at the prompts on the screen. The tooltip will tell you exactly where you are with regards to formula syntax and what is expected next.

Examples:

enter image description here

The cursor is in the middle of an AND() statement. The tooltip shows the current syntax element in bold. We are currently in the first logical condition of the AND(). The next expected entry is a comma and another logical condition. A closing bracket will close the AND() statement.

You continue with a closing bracket, so the AND() statement is closed and the formula continues with the next argument of the encasing IF() statement.

enter image description here

But you think you are still in the AND() function and provide another logical condition, which Excel interprets as the TRUE statement of the IF() function.

enter image description here

Excel now expects the FALSE statement of the IF function and then a bracket that closes the IF statement. You supply a cell reference and then a comma to continue with another IF. By this time you have entered too many arguments for the first IF statement. In the following screenshot, no argument is highlighted. Excel cannot place the current cursor position anywhere in the correct formula syntax.

enter image description here

If you watch the tooltips when you enter a formula, you can see that the AND statement was closed too early. Remove the superfluous brackets and see the tooltip now.

enter image description here

We are now in the FALSE statement of the first IF and start with another nested IF(). Continue in this way and see what the tooltip tells you and you will arrive at the correctly formed

 =IF(AND(A2<H2),(A2>=0),G2,IF(AND(A2<H3,A2>=H2),G3),(IF(AND(A2<H4,A2>=H3),G4),(IF(AND(A2<H5,A2>=H4),G5),IF(AND(A2<H6,A2>=H5),G6)

enter image description here

Watching Excel's helpful tips can save an awful lot of time in troubleshooting.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

In addition to the AND(...) syntax problem previously mentioned, you are closing off the latter IF(...) statements too early.

=IF(AND(A2<H2,A2>=0),G2,IF(AND(A2<H3,A2>=H2),G3,IF(AND(A2<H4,A2>=H3),G4,IF(AND(A2<H5,A2>=H4),G5,IF(AND(A2<H6,A2>=H5),G6, "nothing matched")))))

... or,

=IF(AND(A2<H2,A2>=0),G2,IF(AND(A2<H3,A2>=H2),G3,IF(AND(A2<H4,A2>=H3),G4,IF(AND(A2<H5,A2>=H4),G5,IF(AND(A2<H6,A2>=H5),G6)))))

Put this into a text editor like Notepad (with line wrap OFF) and put your own formula in the line directly below it to compare the syntax.

Improve this answer

Comments