IF THEN Formula with multiple options

I am attempting to setup a Percent Complete column that is assigned a value based on the status column. The Status column is all text whereas the Percent Complete will be a number. I am not sure if that is where my issues are stemming from. I have 11 different status options. I keep receiving the #INCORRECT ARGUMENT SET error. Any help would be appreciated! Below is the formula I currently have:

=IF(STATUS@row = "Awaiting Tax Planner", "9.09", IF(STATUS@row = "Awaiting Data/County file", "18.18"), IF(STATUS@row = "Awaiting Clean File", "27.27"), IF(STATUS@row = "Awaiting Clean File Approval", "36.36"), IF(STATUS@row = "Awaiting Final Proofs", "45.45"), IF(STATUS@row = "Awaiting Proof Print Approval", "54.54"), IF(STATUS@row = "Job Approved-NEED TO SEND TO COD", "63.63"), IF(STATUS@row = "Job sent to COD", "72.72"), IF(STATUS@row = "Job printed and ready to go", "81.82"), IF(STATUS@row = "Job is gone-NEED TO INVOICE", "90.91"), "100")

Best Answer

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    Answer ✓

    The IF statements in your formula need to be nested, meaning in practice that all your closed parentheses for each IF statement will come at the end of the series of IF then statements. Try this:

    =IF(STATUS@row = "Awaiting Tax Planner", "9.09", IF(STATUS@row = "Awaiting Data/County file", "18.18", IF(STATUS@row = "Awaiting Clean File", "27.27", IF(STATUS@row = "Awaiting Clean File Approval", "36.36", IF(STATUS@row = "Awaiting Final Proofs", "45.45", IF(STATUS@row = "Awaiting Proof Print Approval", "54.54", IF(STATUS@row = "Job Approved-NEED TO SEND TO COD", "63.63", IF(STATUS@row = "Job sent to COD", "72.72", IF(STATUS@row = "Job printed and ready to go", "81.82", IF(STATUS@row = "Job is gone-NEED TO INVOICE", "90.91", "100"))))))))))

    Hopefully, I got the right amount of closed parentheses in there...Haha, let me know if that works for you:)

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    Answer ✓

    The IF statements in your formula need to be nested, meaning in practice that all your closed parentheses for each IF statement will come at the end of the series of IF then statements. Try this:

    =IF(STATUS@row = "Awaiting Tax Planner", "9.09", IF(STATUS@row = "Awaiting Data/County file", "18.18", IF(STATUS@row = "Awaiting Clean File", "27.27", IF(STATUS@row = "Awaiting Clean File Approval", "36.36", IF(STATUS@row = "Awaiting Final Proofs", "45.45", IF(STATUS@row = "Awaiting Proof Print Approval", "54.54", IF(STATUS@row = "Job Approved-NEED TO SEND TO COD", "63.63", IF(STATUS@row = "Job sent to COD", "72.72", IF(STATUS@row = "Job printed and ready to go", "81.82", IF(STATUS@row = "Job is gone-NEED TO INVOICE", "90.91", "100"))))))))))

    Hopefully, I got the right amount of closed parentheses in there...Haha, let me know if that works for you:)

  • Yes, it did! And yes, you had the correct number of parentheses. Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!