IF(ISERROR (Excel 2003)

Topic

New Reply

Wondering if someone could assist me with inserting an IF(ISERROR statement into the formula below. I think that my problem is with the brackets, but can’t be sure.

{=AVERAGE(IF((CF_DateOut=FOW),(CF_ProcDays)))}

Thanks!

Reply | Quote

Replies

It’s a little hard to test without data, but try this syntax

=AVERAGE(IF(ISERROR((CF_DateOut=FOW)),0,CF_ProcDays))

assuming you use Ctrl-Shift-Enter to turn it into an {array formula}.

Reply | Quote

Oops…sorry that I did not attached the .xls. Question, when I added this formula to the cell, and the cell was formatted as a number, the end result was 0. If the cell was formatted as general, the formula appeared to calculate the entire range, and did not look at my date criteria (CF_DateOut,RDate). Is there a way to have the end result of this array provide me with an N/A?

Reply | Quote

Does this do what you want (as an array formula)?

=IF(SUM((CF_DateOut=FOW))=0,””,AVERAGE(IF((CF_DateOut=FOW),(CF_ProcDays))))

to leave the result blank if the average results in an error, or

=IF(SUM((CF_DateOut=FOW))=0,#N/A,AVERAGE(IF((CF_DateOut=FOW),(CF_ProcDays))))

to display #N/A.

Reply | Quote

Hans,

This is great!!! Thanks again.

Reply | Quote