[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Excel problem

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
0
wfcrr
Asked:
wfcrr
  • 2
2 Solutions
 
barry houdiniCommented:
If you use this formula somewhere at the top (not in column H)

=MATCH(FALSE,H:H,0)

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
0
 
wfcrrAuthor Commented:
Hey Barry, I am completely stupid when it comes to excel. Where do I place that formula, exactly?
0
 
barry houdiniCommented:
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

=COUNTIF(H:H,FALSE)

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

regards, barry
0
 
gowflowCommented:
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

rgds/gowflow

match-formula.xlsx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now