# Is there an Excel “missing value” or “NA” symbol?

I am just picking up Excel, but have experience with R and Stata. Does Excel have a missing symbol convention? That is, does Excel have something like R's `NA` or Stata's `.`?

For example, I evaluate an `IF()` statement and want to return a value that will be omitted from later calculations I use `"NA"`. Is this the correct approach in Excel? It seems like I am missing a basic concept, but I can't get Google to give me a better answer.

To make this more concrete, I have a conditional like `=IF([@[Div Dummy]]=1,"NA",EOMONTH(A8,0))`. Is there a way to get a numeric "missing value" placeholder so that I don't get warnings about conflicted data types?

-
I think the closest thing is using an empty string... but perhaps someone will surprise me. – Daniel Oct 19 '12 at 16:08
To expound on the OP's question: consider a column of data with several 0's, where the zeros are actually missing. You want an average of the non-zero data. How to go about this? So far all my efforts have resulted in generating #NAME? and similar errors. – ckg Apr 28 at 14:55

You're almost there. You can use NA() in your formulas to return that "error" value. It's very useful when charting because it's not charted (unlike 0).

-
Does it matter that this isn't really an error? I just want to be sure that these don't get incorporated into `VLOOKUP()`s. – Richard Herron Oct 19 '12 at 18:37
OK, I see that this there's also an `ISNA()` counterpart to `ISBLANK()`. Thanks! – Richard Herron Oct 19 '12 at 18:42

You can leave cells empty and use (if I remember correctly) `=isblank()` to do the conditional. And it's worth noting that many numerical functions like `=sum()` and `=product()` will ignore empty cells, ala `sum(x, na.rm=TRUE)` in R.

But no, this is one of dozens of reasons why Excel is not a serious tool for statistical data. (Although it's not terrible for simple simulation and optimization problems.)

-
Thanks, Harlan. I knew that `SUM()` and the gang were smart enough to skip blanks/letters and thanks for the `ISBLANK()` trick (it looks like there's also `ISNA()` for David's answer).I am not a huge Excel fan, but I'm teaching business undergrads, and it's pretty important for them -- want to make sure I'm being precise. – Richard Herron Oct 19 '12 at 18:41
Let me see if I understand you correctly. You recognize that Excel is absolutely the wrong tool for teaching statistics, or at least that you must be very careful trying to use Excel to do statistics. And you are teaching students who perhaps are less sophisticated than some. So how is it exactly that you expect your less sophisticated students to be able to discern the subtleties of Excel's suitability? Use the right tool with these students or don't bother; you can only do harm. – ckg Apr 28 at 14:52