IsError Formula
 

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,MAT CH(Data!L5,Price!$A$3:$A$200,0),MATCH($C5,Price!$A $3:$BI$3,0)))

Thank you!!

-Steph

IsError Formula
 

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

use it in your formula as follows:
=IF(ISERROR(V(Data!K5*INDEX(Price!$A$3:$BI$200,MAT CH(Data!L5,Price!$A$3
:$A$200,0),MATCH($C5,Price!$A$3:$BI$3,0)))),0,V())

--
Regards
Frank Kabel
Frankfurt, Germany


Steph wrote:
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