Strange Excel behavior in some IF functions

Diskutiere Strange Excel behavior in some IF functions im Office Forum im Bereich Microsoft Community Fragen; 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...
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)
 
Thema:

Strange Excel behavior in some IF functions

Strange Excel behavior in some IF functions - Ähnliche Themen

Using Vlookup on other Workbook whichs path is stored in a Cell: Good Day Im Trying to do Vlookup on a closed Workbook whichs path is stored in a Cell. For that im writing a Function which opens the Workbook...
Same self-signed code signing certificate for all users on same computer ?: On my own local PC with Office 2019, I would like all accounts to use the same certificate for Office macros (Word, Excel, Access). Otherwise...
Change my ONLINE word language from german: My online office is in german, it asks if I want to change to swedish, I press yes but nothing changes. My account is set to sweden so how can I...
Copying a SharePoint online document library to another site: Hi Everyone! We have two sites in SharePoint online and want to copy the content of a document library (~1TB) into the library of the other...
Changing theme for just one office program: I am currently starting to use OneNote, I really like the darkmode feature for almost every Office program, except for OneNote as it feels weird...
Oben