► Inconsistent Match Formula - Sample [SOLVED] 2x

I willing to listen to reason which is why I am here and will take alternative possible solutions except VBA at least not at the moment.

My formula is looking for the word "Total" in [Column A] and looking for anything greater than or less than zero in the Subtotal [Column B]. Any time there are descrepancies it will look within column B for potential oppisite matches for the adjustment and will point ↑↓ and tell you the row you should look.

Here is where the formula proves to be incosistent/ #NA (I believe)

Please Login or Register  to view this content.

Please see the attachment included, providing an example of how it should work and how it is not working. I would appreciate any help to make this process better. Thanks!

The problem is that the value in B11 is not exactly equal to -22.80 and thus Excel is not finding a match and is returning #N/A. You can test this by simply typing -22.80 into B11 and all your formula's work
However, even when i expanding the decimals to 16 places, Excel did not show a difference. I'm assuming these numbers were cut and pasted from somewhere else.

Solutions, type in the values or set the spreadsheet to do "Precision as displayed"
Hope that helps.

Thanks for the reply ChemistB, however I do not believe that is correct or maybe I'm misunderstanding you. In order to avoid confusion please see the below spreadsheet. I have included all the formulas, which I apologize they were not in there to begin with, plus the following code:

Please Login or Register  to view this content.

This proves to be TRUE, but why would the following code be True and yet the match formula is incorrect?

Yes, I did the same things. This confused me for quite a while. I am not a programmer and certainly don't know all of Excel's internal algorthyms but the fact remains, that if you type in -22.80 into B11 or, if in another cell you use the formula = ROUND(B11,0) and then paste special>Values that back into B11 or if you set "Precision as Displayed" your formulas work as they should.

Maybe one of the programming gurus can tell you why this difference is invisible to 16 digits and to certain True/False formulas.

Not sure about this, but ...
Try this array formula in C1, Confirm with Ctrl+Shift+Enter not just Enter, Drag/Fill Down

Formula:

Please Login or Register  to view this content.

Is that what you are trying to achieve?

What are the disadvantages of setting "Precision as displayed

That depends. Typically when working with finances, it makes no difference. Often when working with statistical calculations, you want more than what is shown on the screen to be used in the calculation. The general rule of thumb is not to round until you get to your final number.

So, if a calculation came out to 1.234 and you're formatted to 2 decimal places, Excel will only use 1.23 in subsequent calculations and you'll lose some precision. The trick is to figure out how many significant digits you want and format your cells to show them. So if with some financial calculations, you want averages to the half penny, formatt to 3 decimal places instead of 2.