Stupid Excel Trick - amuse your friends & bore your enemies
What’s 1-2?
Today, MS Excel tells me it’s +39815. (Tomorrow it will tell me something different.)
Some while back a signup sheet went around the office for our annual lamb roast. Since every problem (nail) in an office environment has a preinstalled office-suite hammer, the signup list was an Excel spreadsheet. Once everyone has entered their number of guests, it totals up how many total guests. Here’s what I sent around before figuring out Excel’s flavor of crack.
Say you don’t know exactly how many guests you will have for the BBQ. Type a range (e.g. “1-2″) in and see what Excel computes for the total numbers of guests/folks.
For “1-2″ guests by my name I get exactly 39084 total guests. Fixed by just saying “1″, but now I’m curious. How is it computing this number?
I can see that being parsed as “one minus two” and yielding a negative number of guests, but I can’t figure out what particular flavor of crack Excel is smoking to get 39,084. Anyone?
I mean, if it had returned NaN or +32767 (or some other signed-unsigned integer conversion failure), I might have understood.
An Excel guru eventually figured it out: The field was hard-set as a numeric field (no auto). Still, it parsed the expression, not as “one minus two” but as a date - “February 1, current year”, and since it was a numeric field, represented this numerically the best way it knew how: the number of days since January 1st, 1900.
The flavor is strawberry.
cexxy version