### Title

Performing a calculation based on a range of numbers

### Post

I've been unsuccessfully trying to find an answer to my problem, and I'm beginning to pull my hair out!!! :)

This seems so easy to, but I can't wrap my little blonde brain around it. Basically, I need a score of (0-5) to be auto entered into (field B) based on the percentage that appear in (field A)

**Example:**

If (field A) is 0% then (field B) = 0

If (field A) is between 1% and 29% then (field B) should = 1

If (field A) is between 30% and 49% then (field B) should = 2

If (field A) is between 50% and 64% then (field B) should = 3

If (field A) is between 65% and 99% then (field B) should = 4

If (field A) is 100% then (field B) should =5

Please help me ASAP. Thanks all.

snkm:

Thank you for your post.

The calculation by Ninja is excellent (Thank you!), and I'll expand on it a bit for more clarity.

If "field A" is either empty, less than zero, or equal to zero, we want to return 0. This can be written as:

If ( IsEmpty (field A) or field A ≤ 0 ; 0 ; .....

In the next part of the equation, we only need to check and see if field A is less than 30%, because we know field A is not empty and not less than or equal to zero, so it has to be greater than zero. Therefore, the next part of the equation would be:

If ( field A ≤ 30% ; 1 ; .....

The remaining evaluations are straight-forward, but what happens if the value is greater than 100%? For now, we'll leave it as 0.

Putting this all together, the formula could be:

If ( IsEmpty (field A) or field A ≤ 0 ; 0 ;

If ( field A ≤ 30% ; 1 ;

If ( field A ≤ 50% ; 2 ;

If ( field A ≤ 65% ; 3 ;

If ( field A ≤ 100% ; 4 ;

If ( field A = 100% ; 5 ; 0 ))))))

Another way to write this is using a Case () statement. If the first condition isn't met, it jumps to the second condition and so on. You would get the same result with the following formula:

Case ( IsEmpty (field A) or field A ≤ 0; 0 ;

field A ≤ 30% ; 1 ;

field A ≤ 50% ; 2 ;

field A ≤ 65% ; 3 ;

field A ≤ 100% ; 4 ;

field A = 100% ; 5 ; 0 )

There are less closing parenthesis using this method.

Let me know if you need clarification for any of the above steps.

TSGal

FileMaker, Inc.