Using Between Criteria 1

[JohnOB]

I feel like this is a simple thing but can't seem to work it out. I have a formula setup which will change the text in a field to either A, B, C, or D based on a score. At present I have had to enter a formula for each possible score, as below

If Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF}) <= 20 then "D"
Else If Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF}) = 21 then "C"
Else If Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF}) = 22 then "C"

...etc

There are a total of 80 scores, so I want to be able to avoid pasting and changing this 80 times if I can. So what I wanted to do was have 4 entries, 1 for if the score was between 0 and 20, then between 21 and 40 and so on.

I have tried using the IN statement, but perhaps I am not doing it right. I have also tried things like...

If Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF}) = 21 and Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF}) <= 40 then "C"

but it doesn't work

I am sure the between statement can be used, I'm just not sure how to do it.

Thanks very much.

"The only stupid question is the one that doesn't get asked

 

[1973kk]

try this
If (
Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF}) >= 21
and Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF}) <= 40
)then "C

 

[lbass]

Try:

if Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF} < 21 then "A" else
if Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF} < 41 then "B" else
if Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF} < 61 then "C" else
if Sum ({review_answer.score}, {review_answer.REVIEW_HEADERREF} >= 61 then "D"

The clauses are evaluated in order so if a value doesn't meet the criterion of the first clause (< 21), but meets the criterion of the second (<41), then it has to be between 21 and 40, etc.

-LB

 

[JohnOB]

Excellent, that did the job.

The brackets...it's always the brackets!

Thanks very much!

"The only stupid question is the one that doesn't get asked