# How do I create a formula field that displays a text corresponding to a percentage score in another field

Currently, I have fields that have percentage values

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

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.

How might I accomplish this?

• 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. 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. Commented Sep 2, 2022 at 13:47

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'
)
)
)
)
``````