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.

Tags: ,

Leave a Reply