Nov 14 2018 11:45 AM
I run a business using a big honkin' complex Excel worksheet. I've installed several checks to make sure I've entered data correctly.
I get a moderate number of reports from various sources, each containing a detailed statement that represents a revenue from a number of products. I enter the amounts from the statements in one part of my spreadsheet (where the components are totaled up), and I enter the bank deposit amounts in another part. One of my checks is to ensure that the grand totals entered from the statements MATCH one of the bank deposits.
For years, this worked well. But recently, several of the sources have taken to tracking fractions of a penny. So, a bank deposit of, say, $100 might match a statement that has a grand total of $99.9996 or $100.00004. So, my "exact MATCH" formula returns a "N/A" when I would want this to return a MATCH.
I realize that I can use MATCH to return the deposit number of the deposit that is closest to the statement, but to use this feature, I have to choose the match that is closest without going over or the match that is less but not equal to the match. This is not what I want, because if I use this feature, I might match a statement to a deposit that has nothing to do with the statement at all.
What I want is a "close" MATCH function that matches a statement to a deposit +/- $0.005 (or some value I can set). I'm looking for a "close enough" MATCH. How can I do that?
Thanks in advance.
Nov 14 2018 01:50 PM
Hi Jon,
Here is the mockup
=INDEX(B1:B4,MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0))
and in attached
Nov 15 2018 10:50 AM
Thanks. The good news -- your solution appears to work. The bad news -- I don't fully understand your solution. Here are my questions:
So, thank you, but can I get a little more help?
Nov 15 2018 01:30 PM
SolutionJon, for better understanding how the formula works you may stay on the cell with formula (any one) and on ribbon Formulas->Evaluate Formula to see step by step what formula calculates.
Alternatively in formula bar you may select part of the formula which performs this or that calculation, F9 to see result of it and Ctrl+Z to return back.
As for you questions
1) Yes, arithmetic operation of single value and array performs that operation on each element of the array. You may try =SUM({2,3}+2), it returns 9 and intermediate calculation is =SUM({4,5}). Check with evaluate. Thus yes, that part of the formula returns an array like
{abs(a1-b1), abs(a1-b2), abs(a1-b3), abs(a1-b4)}
2) Correct, that will be an array with TRUE and FALSE
3) Double dash transform logical value to its numeric equivalent.
=--TRUE returns 1 (as well as =TRUE*1 and =TRUE+0)
=--FALSE returns 0, similar to above
4) If you need position, not the value on that position, as Detlef mentioned just don't use outer INDEX
=MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0)
In addition, inner INDEX here allows to avoid using of array formula, it returns resulting after ABS calculations array directly to MATCH. Alternatively you may use array formula (Ctrl+Shift+Enter)
=MATCH(1,--(ABS(A1-B1:B4)<=0.05),0)
which returns the same result:
Nov 15 2018 01:43 PM
Okay -- more problems with removing the outer INDEX:
I changed "INDEX(B1:B4,MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0))" to
"MATCH(1,INDEX(--(ABS(A7-B7:B10)<=0.05),0,))",
and I have the following questions:
Please help.
Nov 15 2018 01:52 PM
And what is in B7:B10? Even better if you attach the file with your sample
Nov 16 2018 08:51 AM
The "B7:B10" thingy -- I had copied the original spreadsheet down a few rows, so that I could mess with the formulae without changing the original version.
Using the "=MATCH(1,INDEX(--(ABS(A7-B7:B10)<=0.05),0,),0)" seems to be working. Most importantly, this formula does return a "N/A" if no match was found, because I need that "N/A" to catch when a statement doesn't match a deposit (I use conditional formatting to make that stand out bigly).
One final note: I think the "<=0.05" should probably be "<=0.005" (a half of a penny instead of a nickel. I'll make that change (actually, I'll create a "tolerance variable" and put in a cell, so that I can make changes on the fly.
I'll play with this a bit, including that alternate "MATCH' formula that was suggested. Then, I'll try to implement it in my big honkin' spreadsheet over the weekend. I'll report back.
Thanks to all for the help! I'll report back shortly.
Nov 16 2018 09:32 AM
Jon, when nothing is found instead of returning an error code #N/A it's better to wrap the formula by IFNA or IFERROR and return any text/value you prefer
=IFNA(MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.005),0,),0), "No such")
Nov 17 2018 01:36 PM
I've spent some time today integrating the proposed solutions into my big honkin' spreadsheets, and it appears to be working well.
Of course, it drives me crazy that the grand total of the statements is $0.00000478 different from the deposits. I'm gonna have to pocket that $0.00000478, because I can't figure out how to pay that out.
Thanks for all your assistance, guys.
Nov 17 2018 02:49 PM
Jon, why don't you ROUND to cents your totals and intermediate results? Precision is always exists in calculations, even =1*(0.5-0.4-0.1) is not equal to zero in Excel.
Nov 18 2018 09:15 AM
To answer the question: "why don't you ROUND to cents your totals and intermediate results?" I doing a check to match a statement's total to a list of deposits -- the statements deal with fractions of a cent, and there is no guarantee that rounding errors won't propagate enough that I'd never find a match. I can make all sorts of data-entry errors, often because the statements are printed with teeny-tiny fonts & my eyes aren't so good. So, this check is important for me to catch errors. (Nowadays, now that the statements track fractions of a cent, the statements also come in .CSV format, making it easier to run a pivot table & do a copy-paste of values and thereby lessening my user errors).
Nov 15 2018 01:30 PM
SolutionJon, for better understanding how the formula works you may stay on the cell with formula (any one) and on ribbon Formulas->Evaluate Formula to see step by step what formula calculates.
Alternatively in formula bar you may select part of the formula which performs this or that calculation, F9 to see result of it and Ctrl+Z to return back.
As for you questions
1) Yes, arithmetic operation of single value and array performs that operation on each element of the array. You may try =SUM({2,3}+2), it returns 9 and intermediate calculation is =SUM({4,5}). Check with evaluate. Thus yes, that part of the formula returns an array like
{abs(a1-b1), abs(a1-b2), abs(a1-b3), abs(a1-b4)}
2) Correct, that will be an array with TRUE and FALSE
3) Double dash transform logical value to its numeric equivalent.
=--TRUE returns 1 (as well as =TRUE*1 and =TRUE+0)
=--FALSE returns 0, similar to above
4) If you need position, not the value on that position, as Detlef mentioned just don't use outer INDEX
=MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0)
In addition, inner INDEX here allows to avoid using of array formula, it returns resulting after ABS calculations array directly to MATCH. Alternatively you may use array formula (Ctrl+Shift+Enter)
=MATCH(1,--(ABS(A1-B1:B4)<=0.05),0)
which returns the same result: