Iserror formula

[Steve=True]

Hi All,

I normally write formulas that could error out or be #NA like this:

=if(iserror(really really long formula),"",really really long formula)

I don't like repeating the function as an error checker and then a result.

Is there another way to do this?

Thanks

Steve=True

 

[T. Valko]

Hi All,

I normally write formulas that could error out or be #NA like this:

=if(iserror(really really long formula),"",really really long formula)

I don't like repeating the function as an error checker and then a result.

Is there another way to do this?

Thanks

Steve=True

If you're using Excel 2007 or later the easiest (but maybe not the best) way is to use the IFERROR function:

=IFERROR(your_formula_here,"")

There are other ways that depend on the type of data the formula returns and what result you want to replace any errors.

If you can post the real formula(s) we can be more specific (maybe!). Sometimes you can trap errors without even using any of the error checking functions.

 

[Steve=True]

Thanks T., you are right, i forget about iferror. i will review more about if error, but i generally try to build the spreadsheets to 2003 specs.

Here is my if error statement. The reason that it is #na is that the user has picked a start date that is in the future so no data will display, but instead of #na, i want to display blank ""

Here is my formula before iserror:
=IF(AND(COLUMN()<StatusMonthHighlight,SUMPRODUCT(1*(V6:V25=CHAR(NotStarted))+1*(V6:V25=CHAR(Started))+1*(V6:V25=CHAR(Completed)))>0),IF(SUMPRODUCT(--((V6:V25=CHAR(NotStarted))))>0,IF(StatusMonthHighlight-COLUMN()>1,-1,0),IF(SUMPRODUCT(--((V6:V25=CHAR(Started))))>0,IF(StatusMonthHighlight-COLUMN()>2,-1,0),1)),"")

and after is error:

=IF(ISERROR(IF(AND(COLUMN()<StatusMonthHighlight,SUMPRODUCT(1*(V6:V25=CHAR(NotStarted))+1*(V6:V25=CHAR(Started))+1*(V6:V25=CHAR(Completed)))>0),IF(SUMPRODUCT(--((V6:V25=CHAR(NotStarted))))>0,IF(StatusMonthHighlight-COLUMN()>1,-1,0),IF(SUMPRODUCT(--((V6:V25=CHAR(Started))))>0,IF(StatusMonthHighlight-COLUMN()>2,-1,0),1)),"")),"",IF(AND(COLUMN()<StatusMonthHighlight,SUMPRODUCT(1*(V6:V25=CHAR(NotStarted))+1*(V6:V25=CHAR(Started))+1*(V6:V25=CHAR(Completed)))>0),IF(SUMPRODUCT(--((V6:V25=CHAR(NotStarted))))>0,IF(StatusMonthHighlight-COLUMN()>1,-1,0),IF(SUMPRODUCT(--((V6:V25=CHAR(Started))))>0,IF(StatusMonthHighlight-COLUMN()>2,-1,0),1)),""))

 

[Jonmo1]

Thinking outside the box...

Instead of testing the whole formula for error...
Test for what causes the error...

The reason that it is #na is that the user has picked a start date that is in the future

so test for that..

=IF(StartDate>TODAY(),"",really long formula here)

 

[Steve=True]

Good thought. Thanks all!

 

[Steve=True]

Thank again.

Here is the revised formula where i incorporated a check to see if the status month was an error vs the entire formula.

=IF(ISERROR(StatusMonthHighlight),"",IF(AND(COLUMN()<StatusMonthHighlight,SUMPRODUCT(1*(W6:W25=CHAR(NotStarted))+1*(W6:W25=CHAR(Started))+1*(W6:W25=CHAR(Completed)))>0),IF(SUMPRODUCT(--((W6:W25=CHAR(NotStarted))))>0,IF(StatusMonthHighlight-COLUMN()>1,-1,0),IF(SUMPRODUCT(--((W6:W25=CHAR(Started))))>0,IF(StatusMonthHighlight-COLUMN()>2,-1,0),1)),""))

Great suggestions!