0

Currently, I have fields that have percentage values

enter image description here

For instance, my communication score field that is scored based on a series of questions and has the formula

enter image description here

I want to change the string in my overall field based on my communication score so when my communication score is at 40% my Overall will show Low which corresponds to the table below.

enter image description here

How might I accomplish this?

5
  • Hi H4shBrown. What did you try so far? Commented Sep 2, 2022 at 10:15
  • Hi @GuyClairbois, I tried using several IF statements but somehow I can't get it to work.
    – H4shBrown
    Commented Sep 2, 2022 at 13:37
  • What are the exact values you are getting in your pct field? Whole numbers between 0 and 100 followed by a percentage sign? Commented Sep 2, 2022 at 13:43
  • Also, if you're always diving by 10, why have all those cases on the 2nd part of your formula? Or can someone also enter an answer that is neither yes nor no? Commented Sep 2, 2022 at 13:45
  • @GuyClairbois yes someone can enter an answer that is neither yes or no and when that happens the question is not considered in the division. Hence, when no question is answered at all the return answer is blank. However, when there is a return value (so at least one yes or no answer), I return a percentage value with no decimal places in my formula field.
    – H4shBrown
    Commented Sep 2, 2022 at 13:47

1 Answer 1

2

If you have percentage values (whole numbers) with a decimal, then try the following:

  1. interpret the percentage field as a text field - TEXT()
  2. strip off the percentage sign - SUBSTITUTE() An alternative is working with LEFT() but since the length can be 1, 2 or 3 digits, SUBSTITUTE() is simpler.
  3. interpret the remainder as a number field (so you can work with the ranges) - VALUE()
  4. use an IF() function to distinguish between the different ranges

This would result in something like:

IF(
VALUE(SUBSTITUTE(TEXT( pct_field__c ),'%','')) < 0.60,'LOW',
IF(
VALUE(SUBSTITUTE(TEXT( pct_field__c ),'%','')) < 0.70,'NEEDS IMPROVEMENT',
IF(
VALUE(SUBSTITUTE(TEXT( pct_field__c ),'%','')) < 0.80,'SOLID',
IF(
VALUE(SUBSTITUTE(TEXT( pct_field__c ),'%','')) < 0.90,'HIGH',
'VERY HIGH'
)
)
)
)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .