
1. Re: Performing a calculation based on a range of numbers
ninja Feb 2, 2009 12:36 PM (in response to snkm)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
TSGal Feb 2, 2009 1:49 PM (in response to snkm)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 straightforward, 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
Gecho Apr 29, 2010 6:26 AM (in response to snkm)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
comment_1 Apr 29, 2010 8:57 AM (in response to snkm)Try =
Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; Left ( FieldA ; 3 ) ; 1 ; 1 ) / 3 )