3 Replies Latest reply on Mar 19, 2010 9:39 PM by comment_1

    Calculating Formulas

    spinny

      Title

      Calculating Formulas

      Post

      Hi All,

       

      In my recruiting database I am trying to calculate an academic index. Part of the Academic index is based on a number of points you get for your GPA.  This differs depending on what your GPA is.

       

      What I am trying to do is input a GPA to the GPA4 field.  I then want the Academic Index field to look at the GPA4 field, compare it to a list of values and the points you get for each value in GPACALC table and return the score to the academic index field.  In Excel the formula is:

       

      =VLOOKUP(N6,AX4:AY34,2)

       

      Where N6 is the GPA, AX4:AY34 is the cell references for the GPA's and the points you get for them and 2 is the number of columns in the array.

       

      I had tried

       

      Lookup ( GPA 4 , GPA4::_4GPA_SCORE : GPA4::_4GPA_POINTS )

       

      Working on the same principal but I am guessing something is wrong with the way I am inputting it as it says it does not recognize the field!

       

      Any help would be appreciated,

       

      Andy

       

      Mac OSX 1.6.2 filemaker newbie!

        • 1. Re: Calculating Formulas
          mrvodka

          In your table GPACALC, create records for each grade range and correpsonding points. You should have a lower, upper, and points fields.

           

          Then create a relationship from your main table to this table where:

           

          GPA ≥ lower

          GPA ≤  upper

           

          Noe you can either put the points field directly on the layout or if indeed you want a lookup to put into another field, then your lookup based on this relationship should work.

          • 2. Re: Calculating Formulas
            spinny

            Ok.... got it working.  There is a small problem though.  I dont want to have 180+ values in my gpa table. Is there anyway for it to look at ranges - right now if I put in 3.2 for a gpa it finds the value that corresponds with that and returns the number of points.  If I input 3.26 as a gpa it returns nothing.  You get 63 points for a gpa between 3.2 & 3.29, 64 points for between 3.3 & 3.39 and so on.  I need it to recognize that any number between 3.2 and 3.29 gets the same score.

             

            Thanks

             

            Andy

            • 3. Re: Calculating Formulas
              comment_1

              Your GPA table can look like this:

               

              GPA   Points

              3.2     63

              3.3     64

               

              and the relationship can be:

               

              YourTable::GPA = GPA::GPA

               

              Then set the lookup to copy next lower value if no exact match.

               

               

              BTW, if the pattern is constant you could simply calculate the points as =

               

              Int ( GPA * 10 ) + 31