• Kate O'Brien
  • NEWBIE
  • 0 Points
  • Member since 2021

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
    Questions
  • 1
    Replies
I have created the following cutomer health formula field that works perfectly.

IF( ISPICKVAL( Successful_Health_Check__c ,"No" ) ,0,10 )+
IF( ISPICKVAL( Usage_at_Expected_level__c ,"No" ) ,0,25 ) + 
IF( ISPICKVAL( All_Licenses_Paid_For__c  ,"No" ) ,0,10 ) + 
IF( ISPICKVAL( NPS_Greater_Than_6__c ,"No" ) ,0,20 ) + 
IF( ISPICKVAL( Last_In_Person_Meeting_Time_Frame__c ,"3 Months" ) ,0,0 ) + 
IF( ISPICKVAL( Last_In_Person_Meeting_Time_Frame__c ,"6 Months" ) ,0,10 ) + 
IF( ISPICKVAL( Last_In_Person_Meeting_Time_Frame__c ,"12 Months" ) ,0,30 ) + 
IF( ISPICKVAL( Change_in_Management__c ,"Yes" ) ,0,5 ) +
IF( ISPICKVAL( Overdue_30_Days__c ,"Yes" ) ,0,20 ) +
IF( ISPICKVAL( Overdue_60_Days__c  ,"Yes" ) ,0,35 ) +
IF( ISPICKVAL( Level_2_Support_Tickets__c  ,"Yes" ) ,0,55 ) +
IF( ISPICKVAL( Identified_Red_Flag_Issue__c  ,"Yes" ) ,0,55 )

We are categorizing customers into 3 categories based on the score that is calcuated from this formula.

Green = 0 - 30 
Yellow = 31 - 51 
Red 51- 9999

I would like to create a formula field that indicates their status and gives a visual indicator.  I have the following formula in an opporuntiy field that works well in categorizing Opps by Stage.

IMAGE(
CASE(StageName , 
"Negotiating Price & Implementation", "/img/samples/light_green.gif",
"Qualifying", "/img/samples/light_yellow.gif",
"Closed Lost", "/img/samples/light_red.gif",
"/s.gif"),
"status color")

I am having a tough time modifying that formula to show Red, Yellow or Green based on the following scoring tiers:

Green = 0 - 30 
Yellow = 31 - 51 
Red 51- 9999

Any ideas would be most appreciated