You need to sign in to do that
Don't have an account?
Chad Reynolds
Scoring Field Not Performing As Expected
Good Afternoon!
I have a formula field on the account object that is referencing the NAICS Code to apply a "score" value. I expect the formula to throw the value 42 when the NAICS field is blank, however it is throwing a 95. Any ideas as to what I could be doing wrong based on the formula below? Any help is greatly appreciated!
VALUE(IF(AND(Account_Age_days__c < 180, Account_Age_days__c >= 1),
IF(CONTAINS("221:333:519:523:531",LEFT(NaicsCode,3)),"95",
IF(CONTAINS("525",LEFT(NaicsCode,3)),"85",
IF(CONTAINS("211:237:326:517:533:999",LEFT(NaicsCode,3)),"80",
IF(CONTAINS("236:321:322:325:332:424:518:524",LEFT(NaicsCode,3)),"75",
IF(CONTAINS("113",LEFT(NaicsCode,3)),"72",
IF(CONTAINS("454:511:522:562",LEFT(NaicsCode,3)),"70",
IF(CONTAINS("327:334:481:492",LEFT(NaicsCode,3)),"65",
IF(CONTAINS("487",LEFT(NaicsCode,3)),"62",
IF(CONTAINS("238:337:446:541:561",LEFT(NaicsCode,3)),"60",
IF(CONTAINS("444:493:711:924",LEFT(NaicsCode,3)),"55",
IF(CONTAINS("311",LEFT(NaicsCode,3)),"52",
IF(CONTAINS("313:323:324:622",LEFT(NaicsCode,3)),"50",
IF(CONTAINS("486",LEFT(NaicsCode,3)),"47",
IF(CONTAINS("339:423:442:488:515:551:721:811:812:921",LEFT(NaicsCode,3)),"45",
IF(CONTAINS("312:443:448:451:452:512:621:624:813:925",LEFT(NaicsCode,3)),"40",
IF(CONTAINS("482",LEFT(NaicsCode,3)),"37",
IF(CONTAINS("212:213:315:336",LEFT(NaicsCode,3)),"35",
IF(CONTAINS("453:532:926",LEFT(NaicsCode,3)),"30",
IF(CONTAINS("111:112:115:335:484:485:611:713:922",LEFT(NaicsCode,3)),"25",
IF(CONTAINS("483",LEFT(NaicsCode,3)),"22",
IF(CONTAINS("331:491:623:722",LEFT(NaicsCode,3)),"20",
IF(CONTAINS("928",LEFT(NaicsCode,3)),"17",
IF(CONTAINS("114:441:445:447:712:923",LEFT(NaicsCode,3)),"15",
IF(CONTAINS("316",LEFT(NaicsCode,3)),"12",
IF(CONTAINS("314",LEFT(NaicsCode,3)),"10",
"42"
))))))))))))))))))))))))),
""
))
I have a formula field on the account object that is referencing the NAICS Code to apply a "score" value. I expect the formula to throw the value 42 when the NAICS field is blank, however it is throwing a 95. Any ideas as to what I could be doing wrong based on the formula below? Any help is greatly appreciated!
VALUE(IF(AND(Account_Age_days__c < 180, Account_Age_days__c >= 1),
IF(CONTAINS("221:333:519:523:531",LEFT(NaicsCode,3)),"95",
IF(CONTAINS("525",LEFT(NaicsCode,3)),"85",
IF(CONTAINS("211:237:326:517:533:999",LEFT(NaicsCode,3)),"80",
IF(CONTAINS("236:321:322:325:332:424:518:524",LEFT(NaicsCode,3)),"75",
IF(CONTAINS("113",LEFT(NaicsCode,3)),"72",
IF(CONTAINS("454:511:522:562",LEFT(NaicsCode,3)),"70",
IF(CONTAINS("327:334:481:492",LEFT(NaicsCode,3)),"65",
IF(CONTAINS("487",LEFT(NaicsCode,3)),"62",
IF(CONTAINS("238:337:446:541:561",LEFT(NaicsCode,3)),"60",
IF(CONTAINS("444:493:711:924",LEFT(NaicsCode,3)),"55",
IF(CONTAINS("311",LEFT(NaicsCode,3)),"52",
IF(CONTAINS("313:323:324:622",LEFT(NaicsCode,3)),"50",
IF(CONTAINS("486",LEFT(NaicsCode,3)),"47",
IF(CONTAINS("339:423:442:488:515:551:721:811:812:921",LEFT(NaicsCode,3)),"45",
IF(CONTAINS("312:443:448:451:452:512:621:624:813:925",LEFT(NaicsCode,3)),"40",
IF(CONTAINS("482",LEFT(NaicsCode,3)),"37",
IF(CONTAINS("212:213:315:336",LEFT(NaicsCode,3)),"35",
IF(CONTAINS("453:532:926",LEFT(NaicsCode,3)),"30",
IF(CONTAINS("111:112:115:335:484:485:611:713:922",LEFT(NaicsCode,3)),"25",
IF(CONTAINS("483",LEFT(NaicsCode,3)),"22",
IF(CONTAINS("331:491:623:722",LEFT(NaicsCode,3)),"20",
IF(CONTAINS("928",LEFT(NaicsCode,3)),"17",
IF(CONTAINS("114:441:445:447:712:923",LEFT(NaicsCode,3)),"15",
IF(CONTAINS("316",LEFT(NaicsCode,3)),"12",
IF(CONTAINS("314",LEFT(NaicsCode,3)),"10",
"42"
))))))))))))))))))))))))),
""
))
debra
Blank or Null values can be tricky. Suggest you might test if the value ISBLANK or ISNULL first and set to 42, else continue with your IF(CONTAINS) list.