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...

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...
Office 365 keeps logging in with a messed up version of university account: Hi everybody, I'm using Office 365 as provided through my university. There must be some conflict between different versions of the same user...
Sharepoint Issues with VBA: Hi Guys, We moved our old Harddrive Datas on an mircrosoft cloud, where datas can be retrieved via sharepoint drive Since we moved all our...
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...
Oben