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

    Performing a calculation based on a range of numbers

    snkm

      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
          ninja
            

          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

            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
              Gecho

              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

                Try =

                 

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