ISERROR in formula

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

The following formula does not contain error checking:

=IF($N$25="","",IF((VLOOKUP($Q698,ControlPrePrice,2,0)=0,"",VLOOKUP($Q698,Co
ntrolPrePrice,2,0)))

does anyone know how it can be included?

Thanks
Pat
 
Hi Pat
I think you already received answers to this question. Did they work or
do you still have problems with the solutions provided
Frank
 
Just add another if statement to it. so =if(iserror(IF
($N$25="","",IF((VLOOKUP($Q698,ControlPrePrice,2,0)
=0,"",VLOOKUP($Q698,ControlPrePrice,2,0))), then click on
the "if" you just put in then the "=" sign at the top.
then for true put "" for blank or whatever you want, then
copy the rest of your formula for false.

Should work
 
Hello Frank

It's interesting you should say I "already received answers to this
question"
Would you be perhaps referring to my question "Include error checking"?
This question and heading I have posted twice on this newsgroup and I cannot
understand why I can not see it displayed. Hence my posting it a third time
under the heading "ISERROR in formula"

regards
Pat
 
Hi Pat
they are there :-)
but to solve your problem try
=IF($N$25="","",IF(OR(ISERROR(VLOOKUP($Q698,ControlPrePrice,2,0)),VLOOK
UP($Q698,ControlPrePrice,2,0)=0),"",VLOOKUP($Q698,ControlPrePrice,2,0))
)

Frank
 
Hi Pat
I think both of us provided different formulas to you :-) You're using
to many IFs:
try
=IF($N$25="","",IF(OR(ISERROR(VLOOKUP($Q698,ControlPrePrice,2,0)),VLOOK
UP($Q698,ControlPrePrice,2,0)=0),"",VLOOKUP($Q698,ControlPrePrice,2,0))
)

Frank
 
Hello gentlemen

I was unable to get both of your solutions to work, I am missing a
parenthesis perhaps!

Franks formula:
=IF(iserror(if($N$25="","",IF(or(iserror(VLOOKUP($Q698,ControlPrePrice,2,0),
vlookup($Q698,ControlPrePrice,2,0))=0),"",VLOOKUP($Q698,ControlPrePrice,2,0)
))

watermans formula:
=IF(iserror(if($N$25="","",IF(VLOOKUP($Q698,ControlPrePrice,2,0)=0,"",VLOOKU
P($Q698,ControlPrePrice,2,0)))


regards
Pat
 
Frank

I have found the two posts under the heading "include error checking" and
your replies. This is interesting because I had a previous question with the
same
heading but with a different formula, and that is where my new post went to.

Pat
 
How about avoiding computing the same many times?

=IF($N$25="","",IF(ISNA(SETV(VLOOKUP($Q698,ControlPrePrice,2,0))),"",IF(GETV
(),GETV(),"")))

which requires the free morefunc.xll add-in from:
http://longre.free.fr/english/index.html.

If you can't use morefunc for some reason, substitute V for both SETV and
GETV...

=IF($N$25="","",IF(ISNA(V(VLOOKUP($Q698,ControlPrePrice,2,0))),"",IF(V(),V()
,"")))

V() is a function coded in VBA...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

To add it to your workbook:

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy the UDF above and paste it in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.
 
Frank

I am still getting an #N/A error.

Pat

Frank Kabel said:
Hi Pat
I think both of us provided different formulas to you :-) You're using
to many IFs:
try
=IF($N$25="","",IF(OR(ISERROR(VLOOKUP($Q698,ControlPrePrice,2,0)),VLOOK
UP($Q698,ControlPrePrice,2,0)=0),"",VLOOKUP($Q698,ControlPrePrice,2,0))
)

Frank
Click to expand...
 
This is interesting because I had a previous question with the
same
heading but with a different formula
Click to expand...

This is a bug in your newsgroup reading program (Outlook Express). It
always combines postings with the same Subject, even if they were not part
of the same discussion thread.

So make sure to use a unique subject when posting.
 
Back
Top