3 Replies Latest reply on May 7, 2017 1:19 PM by bookalaka

    Excel import or Filemaker conversion problem?

    MarkB

      Hi All,

       

      I hate it when a client points out a problem I didn't see. I am importing student grades (as %) into FMP from an Excel file the client provides me. The grades are manually entered in Excel with 2 decimal place precision (##.##%). The FMP solution in turn produces a PDF transcript for all the students courses, with their %grade rounded to 1 decimal place precision. No big deal. However, one student had a grade of 91.15% and another grade of 94.15%. When rounded to 1 decimal place, the 91.15% showed as 91.1% while the 94.15% showed as 94.2% and the client wanted to know what kind of rounding was I doing. Since I need to know why strange things happen, I created a brand new Excel file with the 1st column set as a % and entered the 2 grades. I then created a brand new FMP file with only 1 number field. I then did a manual import from my Excel file into the FMP file and I noticed that in the import dialog window (below), FMP was showing the source field value as .91149999999998. After the import, the 94.15% came in correctly. So is the problem with how Excel stores that one value or is it a Filemaker problem with that one number, or is it something else - like how computers store real numbers as opposed to integers? I duplicated this in both FMP-11 and FMP-12. This is running on a Windows XP platform and from Excel 2003.

       

      Regards, Mark

       

      FMP Import.png

        • 1. Re: Excel import or Filemaker conversion problem?
          deal.stephen

          The problem seems to be in the way numbers are stored in or imported from Excel. I can not explain the issue but one solution for you would be to have the teachers enter their grade values as decimal values (ie. 81.15% = 81.15). Do not convert the value to percentages with Excel.

           

          In your grade field on FileMaker you will need to convert your data to the decimal form of your grade percentages. You can do this by entering the calculation below as a Auto-Enter Calculated Value.

           

               Round ( Self / 100 ; 3 )

           

          Be sure to check the Perform auto-enter options while importing box on the Import Options screen. If you have not already you will need to set the Data Formatting for your grade field to Percent and fix your number of decimals to 1. This should provide you with the consistent rounding of your grades.

           

          Depending on your needs you can also create an import field for the raw grade data and then create a separate Calculation Field to give your the output for your PDF transcripts. Use the same Data Format settings on your field and the calculation as above just substitute the name of your raw grade data field for "Self".

          • 2. Re: Excel import or Filemaker conversion problem?
            keywords

            I doubt this is a "problem" with either Excel or FileMaker, but rather a function of rounding. If (1) the marks area rounded to 2 decimal places, and (2) 91.15% was in fact 91.1499999, and (3) 94.15% was in fact 94.1500001, then (4) assuming the further rounding to 1 decimal place is operating on the full figure rather than the rounded version, the first will come out as 91.1 while the second will come out as 94.2. Voila!

             

            So what to do? The first question to ask is whether you still need the full, unrounded number in your system. If you do then you need to store the raw figure somewhere, and manipulate its display or further storage via calculations. If you don't then you could store only the 2-place rounded figure by adding an autoenter calc so that the raw figure replaces itself on import.

            • 3. Re: Excel import or Filemaker conversion problem?
              bookalaka

              Hi I am having the same issue and my percentages and in my overall report FM differs by 5% less. I only really need the rounded number

               

              Example

              My import from excel   .352112676056338

               

              MY filemaker calc with same numbers  .35211267605633800315

               

              Difference  when running calc in FM = 3.15e-18

               

              How is this done?

               

              "If you don't then you could store only the 2-place rounded figure by adding an auto enter calc so that the raw figure replaces itself on import."