Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 Cook Oct 19 '12 at 16:08

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.