Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

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

  1. #1
    Join Date
    9th October 2012
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th October 2012
    Location
    Livorno, Italy
    Posts
    763

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

    attach a sample file for testing

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th October 2012
    Posts
    4

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

    Quote 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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th October 2012
    Location
    Livorno, Italy
    Posts
    763

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    21st August 2012
    Posts
    29

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th October 2012
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th October 2012
    Location
    Livorno, Italy
    Posts
    763

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    9th October 2012
    Posts
    4

    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!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 5
    Last Post: April 4th, 2012, 23:35
  2. Replies: 5
    Last Post: July 17th, 2010, 13:04
  3. Convert Imported Dollars Values to Numbers
    By garyk545 in forum EXCEL HELP
    Replies: 2
    Last Post: October 20th, 2007, 10:00
  4. Remove Numbers From Imported Text File
    By micko_escalade in forum EXCEL HELP
    Replies: 5
    Last Post: March 24th, 2007, 17:10
  5. Imported Numbers Seen as Text
    By joots in forum EXCEL HELP
    Replies: 1
    Last Post: December 4th, 2005, 15:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno