#Incorrect Argument Set"

Tara Lehner

Each of these pieces work on their own but when combined I get "Incorrect Argument Set" and I'm at a loss as to why:

=IF([Percent Complete]5 = 1, "Green", IF([Percent Complete]5 >= 0.25, "Yellow"), IF([Percent Complete]5 < 0.25, "Red"))

Jeff Reisman

The reason for the error in your first attempt was because IF statements require a condition followed by a value if true, followed by a value if false. In the first part of your example, you supply a complete IF statement:

=IF([Percent Complete]5 = 1, "Green", IF([Percent Complete]5 >= 0.25, "Yellow"

It's missing a parentheses set, but it supplies a condition, with a value if true, and a value if false - however the value if false is an incomplete IF statement because IT doesn't supply a value if false. The third IF statement is also incomplete, so it can't function as the if false value for the second IF statement.

With these types of nested IFs, I tend to count up:

=IF([Percent Complete]@row <= 0.25, "Red", (IF([Percent Complete < 1, "Yellow", "Green")))

This says if it's less than or equal to .25, call it red, otherwise, if it's less than 1 call it yellow, but if it's 1 or more, call it Green.

You in effect have an IF statement with a true condition and a false condition, where the false condition is a nested IF statement.

Mike Wilday

@Tara Lehner DId Jreisman27's response make sense to you?

Jeff Reisman

@Tara Lehner Here is a visual guide to the parts of the IF statement, which contains a nested IF statement.

"If this condition is true, set value to Red. If it's not true, consider this nested IF statement: If this other condition is true, set value to Yellow. If it's not true, set the value to Green." So the entire "nested" IF statement is the "if false" value for the first IF statement.

Mike Wilday

@JReisman27 Nice diagram. Hopefully that helps.

Tara Lehner

I was able to get the following to work:

=IF([Percent Complete]6 = 1, "Green", IF([Percent Complete]6 >= 0.25, "Yellow", "Red"))

What I don't understand is WHY. This is clearly my first experience writing formulas and I'm struggling to understand the logic. Thanks in advance!

Andrée Starå

Hi Tara,

Smartsheet looks at the numbers in a column formatted for percent (for example, a % Complete column) as values between 0 and 1.

Use decimal values (examples: 0.25 for 25%, 0.5 for 50%) to get the correct comparison, as opposed to whole numbers.

Your formula:

=

IF([Percent Complete]@row = 1, "Green", = IF it's 100% then Green

IF([Percent Complete]@row >= 0.25, "Yellow", "Red")) = IF it's 25% or more then Yellow and otherwise Red

In your formula with multiple IF functions, it will look at each part and stop looking when it's true, so it can also be important to think about the order.

I replaced the row numbers with @row, so you don't have to think about which row you're on.

Makes sense?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Tara Lehner

Hi Andrée,

Thanks for your response and explanation. It's good for me to know it stops looking when it finds something that is true. I still don't understand why the first formula I presented was generating an incorrect argument set response. Yes, the second formula I posted is perhaps written more clearly, but what is wrong with the logic around the first formula?

Thank you!

Tara

Jeff Reisman

The reason for the error in your first attempt was because IF statements require a condition followed by a value if true, followed by a value if false. In the first part of your example, you supply a complete IF statement:

=IF([Percent Complete]5 = 1, "Green", IF([Percent Complete]5 >= 0.25, "Yellow"

It's missing a parentheses set, but it supplies a condition, with a value if true, and a value if false - however the value if false is an incomplete IF statement because IT doesn't supply a value if false. The third IF statement is also incomplete, so it can't function as the if false value for the second IF statement.

With these types of nested IFs, I tend to count up:

=IF([Percent Complete]@row <= 0.25, "Red", (IF([Percent Complete < 1, "Yellow", "Green")))

This says if it's less than or equal to .25, call it red, otherwise, if it's less than 1 call it yellow, but if it's 1 or more, call it Green.

You in effect have an IF statement with a true condition and a false condition, where the false condition is a nested IF statement.

Mike Wilday

@Tara Lehner DId Jreisman27's response make sense to you?

Jeff Reisman

@Tara Lehner Here is a visual guide to the parts of the IF statement, which contains a nested IF statement.

"If this condition is true, set value to Red. If it's not true, consider this nested IF statement: If this other condition is true, set value to Yellow. If it's not true, set the value to Green." So the entire "nested" IF statement is the "if false" value for the first IF statement.

Andrée Starå

@Tara Lehner

You're more than welcome!

I saw that Jeff answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Mike Wilday

@JReisman27 Nice diagram. Hopefully that helps.

Tara Lehner

@JReisman27 that was exactly what I needed. Thank you!

Andrea Elliott

Good morning,

Thank you for this resource. I've been struggling with an (vlookup) formula that is giving me the Incorrect Argument Set error. I'm not sure what is wrong. I did this last month and did have a problem.

This is my formula. I'm looking at one sheet to pull in sales to another sheet.

=VLOOKUP([Customer ID]1, {3AM - All Access Fee Revenue - for SLAs Range 3,6,false})

Mike Wilday

This is what the formula should look like...

=VLOOKUP([Customer ID]1, {3AM - All Access Fee Revenue - for SLAs Range3},6 ,false)

IF you use that does it work? You may have to delete the cross-sheet reference { } and recreate it. Let me know if it works.