4 Replies Latest reply on Apr 29, 2010 8:57 AM by comment_1

# Performing a calculation based on a range of numbers

### 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.

• ###### 1. Re: Performing a calculation based on a range of numbers

Howdy Snkm,

Thanks for the post, wecome to the forum.

Autoenter calculation:

If(Table1::FieldA=0;0;

If(Table1::FieldA<0;0;

If(Table1::FieldA>0 and Table1::FieldA<30;1;

If( blah, blah, blah....

If(Table1::FieldA=100;5;"" )))))))))))))

validate that field must be "Not Empty" so that if a number falls through all the "Ifs", it'll throw up a flag.

Messy, but works well...is this what you needed?

• ###### 2. Re: Performing a calculation based on a range of numbers

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.

• ###### 3. Re: Performing a calculation based on a range of numbers

Hi,

I am trying to perform a similar function and have set it up as described above. The difference being that I want the function to check the text and then return a number. The function needs to check FieldA for the month in words and return the month in number format to FieldB.

This is what I have...

Case ( IsEmpty ( Month ) ; 0 ;
Month = "January" ; 1 ;
Month = "February" ; 2 ;
Month = "March" ; 3 ;
Month = "April" ; 4 ;
Month = "May" ; 5 ;
Month = "June" ; 6 ;
Month = "July" ; 7 ;
Month = "August" ; 8 ;
Month = "September" ; 9 ;
Month = "October" ; 10 ;
Month = "November" ; 11 ;
Month = "December" ; 12 ; "N/A" )

Please help, or let me know if there is a much easier way of doing this (sorry, I'm very new!)

• ###### 4. Re: Performing a calculation based on a range of numbers

Try =

Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; Left ( FieldA ; 3 ) ; 1 ; 1 ) / 3 )