M
MSCom
Neuer Benutzer
Threadstarter
- Dabei seit
- 20.09.2016
- Beiträge
- 1
Yesterday I was notified about the false calculation results in one of our interface files.
After some research I came to the conclusion that the problem is the strange behavior of Excel in some functions.
Please look at the attached test file and send the test file to the Microsoft Corporation.
I hope to get some explanation of this feature and suggestions for the workaround.
Summary of the problem:
In some cases (e.g. SUMIF, COUNTIF) when comparing the criteria, Excel seems to convert the text to number and compare the numerical values – even if the criteria column is formatted as text and/or the cell value is explicitly specified as text.
This causes false comparison and therefor also calculation results, as in the case of numerical texts the texts with identical first 15 digits are treated as identical despite of the differences on the later positions.
In our case it influences some selling invoice interface files (with long, quasi-numerical invoice IDs); it might also influence some GUID (we use 26 till 32 digits) processing.
=SUMIF(A:A;A7;B:B)
After some research I came to the conclusion that the problem is the strange behavior of Excel in some functions.
Please look at the attached test file and send the test file to the Microsoft Corporation.
I hope to get some explanation of this feature and suggestions for the workaround.
Summary of the problem:
In some cases (e.g. SUMIF, COUNTIF) when comparing the criteria, Excel seems to convert the text to number and compare the numerical values – even if the criteria column is formatted as text and/or the cell value is explicitly specified as text.
This causes false comparison and therefor also calculation results, as in the case of numerical texts the texts with identical first 15 digits are treated as identical despite of the differences on the later positions.
In our case it influences some selling invoice interface files (with long, quasi-numerical invoice IDs); it might also influence some GUID (we use 26 till 32 digits) processing.
=SUMIF(A:A;A7;B:B)
