General thread for random excel hate.
Most recently I ran into this obscure issue:
If you sum/countif over a 16 character string of all numbers, excel will implicitly convert the last character to zero.
Who stores numbers as texts??
Apparently excel doesn’t. That’s who.
1 Like
We unfortunately have identifiers with leading zeros, which means store as text or lose said leading zeros which makes every lookup or index match fail. None of our identifiers are greater than 15 as far as I’m aware… hopefully I’m not mistaken.
To actually answer you, we have some long ID numbers that can have leading zeroes, or letters inside of them, and I was concatenating them to make a lookup.
Again, not a big issue, unless someone doesn’t tell you that they are secretly casting your strings as floats within a formula.
Ugh, now I’m worried about any concatenation that we use sumifs on. I need to check that.
I have 2 columns in a spreadsheet containing dollar amounts that are (or ****in’ should be!!!) identical, but when I subtract, one of the cells has the teeniest, tiniest, non-zero ****in’ number!!! 
1 Like
humble brag about having 16 digit salary?
I wish: it’s part of reserve change components.
Difference of 2.91038E-11. WTH Excel??? 
Just in case you really are wondering, it’s a common problem with a lot of languages that store data in binary and round the last digit.
Try =(.3-.2-.1)
1 Like
It doesn’t affect my totals, but fer sake dammit Excel!!!
1 Like
Yeah, I find I have to round to pennies or something or I get some weird non-zero result when it should be precisely zero. Very annoying, but it’s been that way for ages.
maaaaaybe they’ll stop auto-converting stuff
oh, and re: y’all bitching-and-moaning over floating point:
https://www.soa.org/news-and-publications/newsletters/compact/2014/may/com-2014-iss51/losing-my-precision-tips-for-handling-tricky-floating-point-arithmetic/
I shouldn’t, but I just had to try it.
Interestingly enough, if I take the brackets out, i.e. = 0.-.2-.1 then I get zero.
I admit that my knowledge of floating point issues is not much more than “it exists” but impacted by bracketing that doesn’t change the order of operations?!
I dream of the day when Excel stops deciding it knows better than I do how my numbers should be formatted.
I’m even finding this on old files made 10+ years ago, default format is “date” which has got to be the worst choice ever. Anything that was “general” before is “date” now. Not every file, though, even ones that I think were made at the same time.
2 Likes
So if you typed 1, 2, 3, 4 into A1, A2, etc. it would assume 1/1/1960, 1/2/1960 or something?
Also describing excel stuff feels a bit like mental chess…
“Earned premium sum to D7”
year “1900”, I think
or “1/1/00”
To an optimist, a glass is half full.
To a pessimist, a glass is half empty.
To Excel, a glass is January 2, 1900
1 Like