# #n/a errors

#### niyrho

##### Board Regular
I am using a combination of validation, vlookup and simple formulas to lay out a workorder. If I leave something blank in one of the feilds then everything that comes after say #n/a and won't allow the calculation to complete. Is there a way I can set a default value or something so to make calculate all the feilds that ARE filled in?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### niyrho

##### Board Regular
What is the purpose of having "your formula" on there twice? What do I put for each of them?

#### Banker1

##### Active Member
The first YourFormula is evaluated to see if it returns an error, if no error exists the second YourFormula is executed otherwise "Default Value" is executed. Default value can be anything but will usually be 0, "-", or "na" (assuming you are working with numbers). See below:
Book1
ABCD
30na
412
Sheet1

Edit: If you're getting an #n/a error you need to use this:

Code:
``=IF(ISNA(YourFormula),0,(YourFormula))``

You can replace the ,0, with whatever you want (i.e., "-", "na", etc)

Last edited:

#### niyrho

##### Board Regular
I tryied these codes and couldn't figure out how to get them to do anything. For an example, in one cell I have the code,

=VLOOKUP(F18,Employees!A1:M14,13,FALSE)*24*K18

I have another cell set up to simply add the values of all the cells like that one together. It works fine as long as you choose a value for every cell that code refers to. But if one is left blank, which can't be avoided, it gives me an #N/A error which doesn't allow the later formulas to be completed.

#### Banker1

##### Active Member
Code:
``=IF(ISNA(VLOOKUP(F18,Employees!A1:M14,13,FALSE)*24*K18),0,VLOOKUP(F18,Employees!A1:M14,13,FALSE)*24*K18)``

Will return 0 instead of #N/A

#### DonkeyOte

##### MrExcel MVP
Just in case...

If you're using XL07 MS finally put in the function we've all had as a UDF for the last x years -- namely an error handler for functions removing the need to double evaluate...

So

=IFERROR(VLOOKUP(....),0)

where 0 is what you want to return if the initial calculation returns an error.

#### Banker1

##### Active Member
Just in case...

If you're using XL07 MS finally put in the function we've all had as a UDF for the last x years -- namely an error handler for functions removing the need to double evaluate...

So

=IFERROR(VLOOKUP(....),0)

where 0 is what you want to return if the initial calculation returns an error.

Can you post the code for this UDF please?

#### DonkeyOte

##### MrExcel MVP
Well everyone's is probably slightly different but mine is:

Code:
``````Function f_err(res As Variant, Optional def As Variant)
If IsError(res) = True Then
f_err = def
Else
f_err = res
End If
End Function``````

So from native excel

=f_err(formula,variant alternative if formula fails)

#### niyrho

##### Board Regular
Thanks guys, I used =if(isna code. It worked for the most part. Only problem is I tried using it in multiple cells and it would only work in the first couple. I checked the code in each one and it all looked ok. Everything matched up the way it should, but after the first two rows everything stays defaulted to 0 no matter what I enter.

Replies
7
Views
442
Replies
1
Views
167
Replies
4
Views
137
Replies
9
Views
320
Replies
0
Views
136

1,190,700
Messages
5,982,362
Members
439,776
Latest member
mathewduffy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back