12 Replies Latest reply on Aug 19, 2016 11:22 PM by Suresh

# Required "Values between" Formula Calculation

Hi everyone,

Good day all!

I required a small clarification. Have some values & need to apply a concept.

If my Value(Score) <= 85 means set Field1's input as 1

85 < Value > 95 means 2

95 < Value > 100 means 3

100 < Value > 110 means 4

110 < means 5

So I use case function,

Here my calculation.

Case ( Score ≤ 85; 1; 85 ≤ Score ≥ 95; 2; 95 ≤ Score ≥ 100; 3; 100 ≤ Score ≥ 110; 4; Score ≥ 110; 5)

But I feel Result is coming wrongly, somewhere i did mistake!

Please suggest me where i did mistake!!

Suggestions & Solutions much appreciated!

- Suresh

• ###### 1. Re: Required "Values between" Formula Calculation

Case (

Score ≤ 85; 1;

Score ≤ 95; 2;

Score ≤ 100; 3;

Score ≤ 110; 4;

5

)

• ###### 2. Re: Required "Values between" Formula Calculation

Actually While i try Score 110 also Result is "2"

- Suresh

• ###### 3. Re: Required "Values between" Formula Calculation

It shouldn't. Check your syntax carefully.

• ###### 4. Re: Required "Values between" Formula Calculation

Is the field 'Score' set to return a result type 'Number'?  As text, 110 is indeed less than 95 (although it is also less than 85...)

• ###### 5. Re: Required "Values between" Formula Calculation

I stored it as number only, please checkout my script workspace here as image.

• ###### 6. Re: Required "Values between" Formula Calculation

Yes keywords, i cant recognize where i did mistake, can you identify & suggest me please!

• ###### 7. Re: Required "Values between" Formula Calculation

Why did you not use the formula I posted?

• ###### 8. Re: Required "Values between" Formula Calculation

The & character does not represent a logical 'AND' - it is a text function that strings text together, like 'FirstName & " " & Surname'

If you want to check for two conditions you would use:

Score  > 90 and Score < 100

You must re-state the field in each condition, so this does NOT work:

Score  > 90 and < 100

But the Case Statement I suggested is easier and clearer to read.

• ###### 9. Re: Required "Values between" Formula Calculation

See Above, I tried your's actually! Reference Link

Suresh wrote:

Actually While i try Score 110 also Result is "2"

- Suresh

Anyother ideas?

• ###### 10. Re: Required "Values between" Formula Calculation

I got a idea from your state, its working good!

Case ( Score > 95 and Score <100; 3 )

Thank you so much for your help!

- Suresh

• ###### 11. Re: Required "Values between" Formula Calculation

Case (

Score > 109; 5;

Score > 100 ; 4 ;

Score > 95 ; 3 ;

Score > 85 ; 2 ;

1

)

1 of 1 people found this helpful
• ###### 12. Re: Required "Values between" Formula Calculation

Ya! This too nice method!! Thanks BruceRobertson