#n/a errors

[niyrho]

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?

 

[Guanjin Peter]

=if(YourFormula, Your Formula, "Default Value")

 

[niyrho]

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

 

[Banker1]

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
	A	B	C	D
2	Valuetoevaluate	Answer
3	0	na
4	1	2
Sheet1

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

=IF(ISNA(YourFormula),0,(YourFormula))

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

 

[niyrho]

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]

=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]

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]

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]

Well everyone's is probably slightly different but mine is:

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]

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.