Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Nest IF limit or error in statement
Hi Folks -
I'm trying to write a long formula that evaluates nearly 40 criteria then based on that criteria assigns a score. Is there a limit on the number of IF statements and if so what is it? If there isn't, then what am I doing wrong here? OR is there a better way to do this?
The error I get is #INVALID OPERATION (no help whatsoever)
=IF([Impacted Area]1 = "P&L Alignment", 3, IF([Impacted Area]1 = "Org Alignment", 3, IF([Impacted Area]1 = "Customer 360", 2, IF([Impacted Area]1 = "Case Mgmt", 2, IF([Impacted Area]1 = "Red Accounts", 3, IF([Impacted Area]1 = "CIC", 2, IF([Impacted Area]1 = "Missile Command", 2, =IF([Impacted Area]1 = "Help & Training in 62org", 2, IF([Impacted Area]1 = "Escalation PoC Published", 3, IF([Impacted Area]1 = "Cloud Services Portfolio", 3, IF([Impacted Area]1 = "Success Plans", 3, IF([Impacted Area]1 = "Advisory", 2, IF([Impacted Area]1 = "Journeys", 1, IF([Impacted Area]1 = "OneSM", 3, IF([Impacted Area]1 = "CFL Assist", 3, =IF([Impacted Area]1 = "Accelerators", 2, IF([Impacted Area]1 = "Field Implementation", 2, IF([Impacted Area]1 = "Specialist Alignment", 2, IF([Impacted Area]1 = "Customer communications", 3, IF([Impacted Area]1 = "Renewals", 3, IF([Impacted Area]1 = "Service Ops", 2, IF([Impacted Area]1 = "PSE", 3, IF([Impacted Area]1 = "SOW/uPSA Aligned", 3, IF([Impacted Area]1 = "Rate Cards Aligned", 3, IF([Impacted Area]1 = "Ops Reports & Dashboards Updated", 1, IF([Impacted Area]1 = "Enablement", 2, IF([Impacted Area]1 = "101 Content Available", 2, IF([Impacted Area]1 = "201 Content Available", 2, IF([Impacted Area]1 = "Skills Database Updated", 1, IF([Impacted Area]1 = "Service Delivery - Projects Aligned to Regions", 2, IF([Impacted Area]1 = "Service Delivery - Utilization Tracking & Reporting", 2, IF([Impacted Area]1 = "Service Delivery - Universal Staffing Pool/Process", 1, IF([Impacted Area]1 = "Services Selling - SP/SA & S4 Enabled", 2, IF([Impacted Area]1 = "Opptys in Org62 Dashboards", 1, IF([Impacted Area]1 = "EWS", 1, =IF([Impacted Area]1 = "EWS", 1, IF([Impacted Area]1 = "Scorecard & Comp Plans", 2, IF([Impacted Area]1 = "Support", 2, ""))))))))))))))))))))))))))))))))))))))
Comments
-
FIXED IT - had a few "="
-
FYI: There is a limit to the number of characters allowed in formula.
I have never hit it, but know it exists. I think it is 750, but am not sure. Rob Hagan knows, but I could not find where he posted it.
Do you do this for every row, or only row 1?
~40 criteria, with scores 1, 2, 3, or blank?
Craig
