Excel question

El Mucho

Hey I know there are a few resident excel experts here I have a quick question.

Is there a way to set an #N/A output to be blank in a cell or equal to zero? The #N/A is only appearing because it references another cell that is blank at the moment but may not be in the future.

L Ron Howard

=IF(A1<>"",A1,0)

tastydonuts

assuming 2007 or later:

=if(isna(<formula>),0 [or ""], <formula>)

El Mucho

Sweet, thanks all for the help, that worked wonderfully.

Dr. Frenchenstein

That formula, and vlookup should be taught in every school ever.

tastydonuts

the more I think about it, iserror() may be better to use instead of isna()...

edit: that would capture the other possible errors it could have.

Drez

ISERROR and ISNA AREAWESOME

LaOs

I've found that often ISERR will miss some things, shooting out #N/A errors. To get them all, use both ISNA and ISERR.

[Edit]
Then, of course, you will never have errors messages appear (you'll only see what you tell it to output in the case of those errors), so you need to be careful that you know what's going on and can still diagnose an error when things aren't working the way you expect.

Dr. Frenchenstein

I'd stick to ISNA, because you're expecting that to pop up, and want a zero there. ISERR might give you a zero for a bigger issue, like LaOs said.

LaOs

Exactly. If you are running into a problem and #N/A is constantly showing up, you only need to check for it with ISNA. That way, other errors (like #DIV/0 errors) will still show up.

[Edit]
Basically, I've found ISNA only finds errors that will result in #N/A while ISERR will find all other errors. If you're only concerned with the #N/A stuff, just stick with ISNA.

El Mucho

Yeah, the particular error will only ever be an #N/A error so I will stick with that for now. But I am glad you mentioned ISERR because I was completely ignorant of that function (as well as ISNA), so thanks for bringing those to my attention.

El Mucho

I have another question.

I have the following formula which is indexing another sheet to pull values:

=INDEX('Sample Info'!C$2:C$200,MATCH($D28, 'Sample Info'!$A$2:$A$200), 0)

If there are no values on the sheet being indexed then the #N/A error is returned (which is fine) but I would like to be able to do what was done above and make it blank if there is nothing to reference (It makes the data table look tidier). I have tried to insert the IF(ISNA(... into the above formula but I can't seem to get it to work.

Do you guys have any recommendations?

Thanks again.

LaOs

Basically, take that whole INDEX formula and put it into the ISNA. Let's call that formula "cookies" and you'll see.

=IF(ISNA(cookies),"",cookies)

That will first check if "cookies" gives #N/A error. If it does, it results in "". If it doesn't, it gives the output of "cookies".

LaOs

Although, your INDEX formula appears to be attempting to reference the "0th" column in the array (which will likely cause an error). And, you should specific the exact match in the MATCH formula (so change it to MATCH($D28, 'Sample Info'!$A$2:$A$200,0) to be totally careful).

Since your array for the INDEX is just column C, you probably just want to change that 0 to a 1.

El Mucho

I tried =IF(ISNA(cookies),"",cookies) but it returned an incorrect formula and i couldn't figure out what was wrong.

Is that because I have a 0 instead of 1?