# Formula field image BETWEEN numbers

I am having some trouble with a happiness meter formula field based on NPS score in our surveys.

Here is what I have so far:

``````IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"),
IF( NPS__c <= 8.99 || NPS__c > 7, IMAGE("/resource/Misery05", "Neutral"),
IF (NPS__c <= 6.99 || NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"),
TEXT(1)
)))
``````

Promoter and Neutral work for these ranges. But the check for Detractors returns the Neutral image. I see that || is evaluating AND. Any ideas on a better approach?

Try this

``````IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"),
IF( NPS__c > 7, IMAGE("/resource/Misery05", "Neutral"),
IF( NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"),
TEXT(1)
)))
``````

The function IF behaves as follows IF(logical_test, value_if_true, value_if_false)

In your example, suppose NPS__C is = 1

Your first test is IF (NPS__c >= 9 .... this is not true, so we go to the value if false

For that you have IF( NPS__c <= 8.99 || NPS__c > 7 .... which is saying is NPS__c less than or equal to 8.99 or is NPS__c greater than 7. The value 1 satisfies this as true, because 1 <= 8.99 and so you get the "neutral" image instead of the "Detractor" you wanted.

• That's simplest way !! :) – C0DEPirate May 5 '16 at 14:51
• Also, this avoids situations where the field is say, 8.995, which is more than 8.99 and less than 9. – sfdcfox May 5 '16 at 15:33

The problem is that in your check on Neutral, you as is the NPS < 8.99 OR > 7. A value of 5 (for example) would be less than 8.99 so the or evaluates true.

I would write it this way:

``````IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"),
IF( NPS__c >= 7, IMAGE("/resource/Misery05", "Neutral"),
IF( NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"),
TEXT(1)
)))
``````

It won't reach the next check unless it fails the first criteria (you don't need the < 8.99 check). The other option is to use && instead of || as follows:

``````IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"),
IF( NPS__c <= 8.99 && NPS__c > 7, IMAGE("/resource/Misery05", "Neutral"),
IF (NPS__c <= 6.99 && NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"),
TEXT(1)
)))
``````