Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Imported numbers don't match ones calculated by an Excel formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Imported numbers don't match ones calculated by an Excel formula

    Hi,

    1st time post and I've got something that just doesn't make sense.

    I'm importing data from a Yahoo group website that I use to calculate monthly dues for a group of associates.

    I had an issue a while ago where someone didn't enter their data correctly and I'm trying to write an audit script to make sure all the numbers are correct before doing the billing.

    Here's an example of the CSV data that I'm importing:
    "09/01/2012", "Big Bird", "717.32", "718.87", "1.55", "801.8", "803.4", "1.6"
    "09/07/2012", "Big Bird", "718.87", "720.21", "1.34", "803.4", "804.6", "1.2"

    Here is how the data looks after I import it:
    Date Name Tach time-in Tach time-out Flown Ttime Hobbs time-in Hobbs time-out Flown Htime
    9/1/2012 Big Bird 717.32 718.87 1.55 801.8 803.4 1.6
    9/7/2012 Big Bird 718.87 720.21 1.34 803.4 804.6 1.2

    So I want to have a formula that verifies that 718.87-717.32 really equals 1.55.

    Problem is when I calcuate 718.87-717.32 and compare it to the 1.55 that I imported, they don't match!

    I've confirmed that all the cells holding the numbers I imported are set to Number and increased the precision of the numbers to make sure I wasn't getting a rounding error (which seemed unlikely due to the simple numbers I'm using).

    Any ideas?

    Regards,
    GregInMI

  • #2
    Re: Imported numbers don't match ones calcuated by an Excel formula

    attach a sample file for testing

    Comment


    • #3
      Re: Imported numbers don't match ones calcuated by an Excel formula

      Originally posted by patel View Post
      attach a sample file for testing
      Here is TXT file I'm importing and how the data looks after importing/cleaning it up.

      Also, I put my calculations and compare in columns I and J.

      Regards,
      Greg
      Attached Files

      Comment


      • #4
        Re: Imported numbers don't match ones calculated by an Excel formula

        Instead of formula =IF(I2=E2;"Match";"Nomatch") use =I2-E2 without IF
        I think it's a calculating precision problem due to csv import

        Comment


        • #5
          Re: Imported numbers don't match ones calculated by an Excel formula

          Are you using "text to columns" ?
          Because when I imported CSV files earlier, the data didn't get the correct "comma" when I used the "text to columns" function. So I had to change that.
          I'm working in a norwegian excel though, dunno if it's the same with the english version

          Comment


          • #6
            Re: Imported numbers don't match ones calculated by an Excel formula

            @Patel - My goal is to validate the numbers that people have entered into a database that is being exported as a CSV text file. People are entering the numbers manually and I want to audit them before calculating the money they owe. The problem is my audit routine is failing because it can't compare my calculated value with the imported value correctly. I think your idea about the calculating precision is correct, but I can't figure it out. I've tried to increase the number of decimal places to make sure there wasn't any rounding going on and the numbers are very simple. Thanks for looking.

            @danielsto - I'm doing an import of a CSV text file so no "text to columns" is being used.

            Comment


            • #7
              Re: Imported numbers don't match ones calculated by an Excel formula

              use this formula
              =IF(I2-E2<0,0001;"Match";"Nomatch")

              Comment


              • #8
                Re: Imported numbers don't match ones calculated by an Excel formula

                @Patel - That did the trick. Must be a numeric precision issue and I see how you changed the IF around to check for a leftover value after doing the I2-E2. Thanks!

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X