globally change average formula to avoid errors

Hi

Would anyone well ne able to help me with the following problem?

I've got a worksheet in which every cell contains a formula based on cells in other worksheets.

For example: =AVERAGE('Team 1 Q1'!AG24:BI24) .

There are hundreds of other cells in the worksheet that have similar formulas to this.

I would like to change this formula and every other formula in the worksheet to:
=IF(ISERROR(AVERAGE('Team 1 Q1'!AG24:BI24)),"",(AVERAGE('Team 1 Q1'!AG24:BI24)))

or, to whatever the equivalent may be eg.

=AVERAGE('Team 1 Q1'!B167:AF167) would become:

=IF(ISERROR(AVERAGE('Team 1 Q1'!B167:AF167)),"",(AVERAGE('Team 1 Q1'!B167:AF167)))

The cells are not in any 'order' so I can't just change one formula and then drag down to change the ones below it. Is their some sort of really advanced Find and Replace I can use?

Thanks once again for your help.

Cheers

Trevor

I think the answer to your question is that Find & Replace should work. I can't see why you need advanced. I also wonder why you need to do this. (With utmost respect). Can you drag and copy formula?

It seems you have encountered div zero problems I wonder if it might be easier to resolve zero problem before getting error in average calculations. The data might be more "grouped together" at this point.

Cheers Richard (Also Melb Oz)

Thanks for the reply Richard.

I can't just do a Find and Replace because I have hundreds of cells with totally different formulas in them. How would I change:

=AVERAGE('Team 1 Q2'!B179:AE179) to =IF(ISERROR(AVERAGE('Team 1 Q2'!B179:AE179)),"",(AVERAGE('Team 1 Q2'!B179:AE179)))

and

=AVERAGE('Team 1 Q3'!BL167:CO167) to IF(ISERROR(AVERAGE('Team 1 Q3'!BL167:CO167)),"",(AVERAGE('Team 1 Q3'!BL167:CO167)))

without doing two separate Find and Replaces.

And I can't drag the cells down because they don't relate to each other that way. It would come up with the totally wrong amount.

Cheers

Trevor

OK starting to see the problem.

Can you post a sample of your work sheets. How big is your .xls file?

To solve the problem I would try to find a way to bring the relevant cells together. I can't imagine how to start because I don't know your structure & logic.