replace #DIV/0 with dash "-"

[Buck4145]

Good afternoon, I've been puzzling over this for quite a bit now. I have this formula which works perfectly untill such times as a nil value is part of the base data (as part of a seperate formula):

=AVERAGE(IF('Archived Trainees'!H8:H38="074-0",IF('Archived Trainees'!L8:L38="Complete - RTU",'Archived Trainees'!AP8:AP38))).

I know that I have to add in [Value if False] or an ISERROR however wherever I put it I get an error. Basically instead of #DIV/0 i would like it to return a dash "-". Thanks in advance.

Working from excel 2003.

 

[davesweep]

Hi,

Like this:

=IF(ISERROR(formula),"-",formula)

in your case,

=IF(ISERROR(AVERAGE(IF('Archived Trainees'!H8:H38="074-0",IF('Archived Trainees'!L8:L38="Complete - RTU",'Archived Trainees'!AP8:AP38)))),"-",AVERAGE(IF('Archived Trainees'!H8:H38="074-0",IF('Archived Trainees'!L8:L38="Complete - RTU",'Archived Trainees'!AP8:AP38))))

 

[drp92]

Since you are running 2003, the iserror will not work as normal formula instead you can use somewhat like this:
=IF(ISERROR(your_formula),0,your_formula)
and since you want "-" you can replace 0 with the same:
=IF(ISERROR(your_formula),"-",your_formula)

Hope this helps !

Also try this thread:
http://www.mrexcel.com/forum/excel-questions/528829-if-error-function-excel-2003-a.html

 

[Buck4145]

Thankyou for this, I have one more issue to the above. I need to insert a condition ('Archived Trainees'!$F$8:$F$500<=DATE(2013,8,1) However I'm unsure where to place it within the formula. I have used this within the SUMPRODUCT and it works however wherever I place it in the below it comes up with an error.

=IF(ISERROR(AVERAGE(IF('Archived Trainees'!H8:H38="074-0",IF('Archived Trainees'!L8:L38="Complete - RTU",'Archived Trainees'!AP8:AP38)))),"-",AVERAGE(IF('Archived Trainees'!H8:H38="074-0",IF('Archived Trainees'!L8:L38="Complete - RTU",'Archived Trainees'!AP8:AP38))))

Cheers for your continued help

 

[Buck4145]

Disregard, figured it out. Thanks anyway.