replace #DIV/0 with dash "-"

Buck4145

New Member
Joined
Sep 10, 2014
Messages
13
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.
 
Last edited:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

davesweep

Well-known Member
Joined
Apr 30, 2007
Messages
510
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))))
 

Buck4145

New Member
Joined
Sep 10, 2014
Messages
13
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,419
Messages
5,547,821
Members
410,813
Latest member
Vhinzvirgo
Top