IsError Formula

S

Steph

Hello everyone. I'm trying to make a bunch of my formulas more simple
to understand and audit. I use the IsError function a lot to remove
N/A errors during a Lookup. Is there a cleaner way to replace an N/A
with a Zero? Below is an example. It seems crazy to double the
length of the entire formula to simply remove an N/A error. Any
ideas?

=IF(ISERROR(Data!K5*INDEX(Price!$A$3:$BI$200,MATCH(Data!L5,Price!$A$3:$A$200,0),MATCH($C5,Price!$A$3:$BI$3,0))),0,Data!K5*INDEX(Price!$A$3:$BI$200,MATCH(Data!L5,Price!$A$3:$A$200,0),MATCH($C5,Price!$A$3:$BI$3,0)))

Thank you!!

-Steph
 
F

Frank Kabel

Hi Steph
for storing a variable have a look at
http://makeashorterlink.com/?J32E23767
(option 2+3 -> the latter one reposted below)
-----
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
 
Top