Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
up vote 3 down vote accepted

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).

share|improve this answer
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.)

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .