15 de octubre de 2008

When you type an equal sign and a function name (for example "=SUM") and press Enter, you get the #NAME? error, because Excel "thinks" that you want to work with a name, wich is not defined.

Or not?

A few days ago, I was working on a model involving date calculations. Since I wanted to calculate the number of labor days between two dates, I used the NETWORKDAYS function.However, while writing the formula, I (accidentally) hit Enter right after the function name (i. e. "=NETWORKDAYS", Enter). Unexpectedly Excel returned:

840368184

Where that number comes from, what does it refers to, or why I got preciselly that number and no other, is something that I completely ignore. Since this behavior was totally unexpected, I wanted to repeat it in other machines. Same result. I wondered if this would happen too with other functions, so I tried with =MAX, =MIN, =OFFSET, =RIGHT, =MID, and some others. In all this cases I got the expected #NAME? However, trying with =CONVERT gave me:

638844971

Other results were:

=WORKDAY, 1679294519

=CONVERT, 638844971

There's even negative results. =WEEKNUM returns -601489317.

I concluded that this is an exclusive behavior of the Analysis Toolpak add-in functions (for a complete list of functions and results, click here).

Continuing with these tests in my lab (sure...), I tested now with other add-ins functions I've downloaded from the web. For example, with =COUNTDIFF I got: 1769668796. With all the functions of my add-ins I watched the same behavior. After this, I tried with some UDF's. In all cases I got #NAME?, so I had to modify my original theory: This behavior ocurrs with any function that belongs to any add-in (ATP or any other), but it doesn't happen nor with built-in neither user defined functions.

Since all this was kind of an oddity to me, I sent an e-mail to John Walkenbach (brief Spanish biography). This was my original message:

Hi John:

I entered this “formula”:

=CONVERT

Notice that I didn’t put the parenthesis. Excel returned:

638844971

That happens only whit add-in’s functions. With any other built-in function Excel returns, as usual, #NAME?

Other examples:

=NETWORKDAYS produces 840368184

=WEEKNUM, -601489317

=UNIQUEVALUES, -1451032386

=WORKDAY, 1679294519

Always the syntax =[add-in function] (no parenthesis)

I think this is kind of an oddity. Or, if you may explain me where those numbers came from…

Thank you.

This was his reply:

That's pretty strange. It doesn't happen in Excel 2007 because the ATP functions are now built-in.

I have Excel 2003 installed, but I didn't install the ATP. I'll see if I can find the original CD and install the ATP to check it out.

Regards,John

If any reader have some idea of why Excel returns this mysterious numbers, please post your comments.