Excel problem

Posted on 2011-10-04
Last Modified: 2012-05-12
See the attached excel sheet. One of you brainiaks came up with formula I have in Column H.  All I know how to do is paste it into column H and then go find the differences.  It helps me find numbers that don't match and I use this to reconcile our Amex account. Anyway, today something is wrong.  Normally after I run the formula in column H, it will say "false" when it encounters a difference. I have isolated all the difference and removed them to Column E.  Now that I have done that, column F should have the same total as column G.  But they are off by a couple dollars. Why?  Maybe I am just stupid today, but can't seem to find any cell in F or G that doesn't match. match-formula.xlsx
Question by:wfcrr
    LVL 50

    Expert Comment

    by:barry houdini
    If you use this formula somewhere at the top (not in column H)


    That will tell you the first row with FALSE in column H - I get 2292 - that row and the next 4 are all FALSE

    regards, barry

    Author Comment

    Hey Barry, I am completely stupid when it comes to excel. Where do I place that formula, exactly?
    LVL 50

    Accepted Solution

    Anywhere but column H - it doesn't matter, I put it in L1 and, like I say, it gave me the result 2292, which means your first FALSE in column H is in H2292. I looked down there and then I saw that you had 5 FALSEs all together.

    To check those were the only ones I placed this formula in L2


    that gives me that tells me those are the only FALSE entries in column H. Those account for the 2.19 discrepancy between the columns

    regards, barry
    LVL 29

    Assisted Solution

    Looking at your question and at your file I think you have a problem with your formulas in Col D,E,F,G,H they are all off by 1 line !!!
    Here is Row 1
    Col A            Col B             Col C            Col D            Col E            Col F            Col E
    Column3      Column3      TRUE      -349.4      -80803.53      -80803.53      TRUE

    I guess you need to delete A1 and B1 and shift them up and adjust the fomula in C1 to refer to a1=b1 and in H also
    I have done it in the attached file. Now why there is a diffrence I suspect maybe when you pasted the values maybe you have something like this i row down .. ? I adapted a filter so you can select only the false in col H to view all false items discreancy



    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now