Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding =if(iserror to formula | Excel Discussion (Misc queries) | |||
Formula Help - Lookup, if, iserror?? | Excel Discussion (Misc queries) | |||
Adding ISERROR to formula | Excel Worksheet Functions | |||
Using iserror in formula | Excel Discussion (Misc queries) | |||
Using ISERROR to Solve #DIV/0 in a formula | Excel Worksheet Functions |