Formula field image BETWEEN numbers

Question

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?

Answer 1

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.

Answer 2

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