Forum Discussion
Serious bug with certain decimal numbers: Countifs and Roundup failed
In addition, Excel works by default with 15 digits and many functions ignore extra digits, but some work in background with 17 digits. More about that is here 17 or 15 digits of precision - Excel Tips - MrExcel Publishing
For this particular case if use
=SUMPRODUCT( ($C$1:$C$11<=C4)*($B$1:$B$11=B4))instead of COUNTIFS() it returns correct result.
NikolinoDE wrote: ``The moment you do this, the number is truncated to 15 digits and shown as exponential.``
Hey, Niko, I think you responded to the wrong thread. If you agree, you can delete your response, and I'll delete mine.
Nothing about Viz's problem has anything to do with trying to enter more than 15 significant digits.
He is entering date and time perhaps in the form 2/1/2020 8:49:48 AM. At least, that is how column C appears to me, due to my regional settings. (My date is in the form MDY.)
If the author thinks that the different behavior of the cells is not based on this (different formatting) and my comment for you means that it is bad / wrong / annoying, I of course take back my comment and apologize for the inconvenience.
You do not need to delete yours as compensation.
Comments are there to help further, to suggest possible solutions,
to reveal other solutions and so much more.
Comments are not there to disturb or irritate or to fulfill any end in itself.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)