Why Isn’t My Excel Function Working?! (MS-Excel)

Even an old data scientist can learn a new trick every once in a while.

Today was such a day.

Imagine my shock, as I spent about two and a half hours trying to get the most basic MS-Excel Functions to correctly execute.

This brings us to today’s example.

I’m not sure if this is now a default option within the latest version of Excel, or why this option would even exist, however, I feel that it is my duty to warn you of its existence.

For the sake this demonstration, we’ll hypothetically assume that you are attempting to write a =COUNTIF function within cell: C2, in order assess the value contained within cell: A2. If we were to drag this formula to the cells beneath: C2, in order to apply the function to cells: C3 and C4, a mis-application occurs, as the value “Car” is not contained within A3 or A4, and yet, the value 1 is returned.

If this “error” arises, it is likely due to the option “Manual” being pre-selected within the “Calculator Options” drop-down menu, which itself, is contained within the “Formulas” ribbon menu. To remedy this situation, change the selection to “Automatic” within the “Calculator Options” drop down.

(Click on image to enlarge)

The result should be the previously expected outcome:

Instead of accidentally and unknowingly encountering this error/feature in a way which is detrimental to your research, I would always recommend checking that “Calculator Options” is set to “Automatic”, prior to beginning your work within the MS-Excel platform.

I hope that you found this article useful.

I’ll see you in the next entry.

-RD