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

Strange behavior when using Sharepoint WebDAV: Hello, we are using Explorer View to access files on a SharePoint Online site. More specific a Teams SharePoint site. We are accessing the site...
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...
Change Custom properties after restricting editing: Hello all, is there a way to access Custom properties of a word document (file -> info -> properties -> advanced properties -> custom tab) after...
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...
Oben