Nest IF limit or error in statement

JDigrazia

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, ""))))))))))))))))))))))))))))))))))))))

JDigrazia

FIXED IT - had a few "="

J. Craig Williams

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