ignore #DIV/0!

Looked online can't find an answer to this seemingly easy question!

i have multiple cells in a row with formula's some showing #DIV/0! as no data yet to make the formula work. at the end of these 200 or so rows i want the average but obviously don't want it to return #DIV/0!. other than deleting all the cells with #DIV/0! what formula can i use to tell excel to ignore cells with #DIV/0!

Well the best way is to sort the formulas in the first place

using the current formula that produces the error

amend to if(iserror(current formula),"",current formula)

Failing that enter the following as an array (hold down shift control and then press enter)

=AVERAGE(IF(ISERROR(a1:a14),"",a1:a14))

Adjust the range accordingly

Regards

Dav

Tried both suggestions you made

=AVERAGE(if(iserror(HF32:HK32,GX32:HC32,GP32:GU32,GH32:GM32),"",HF32:HK32,GX32:HC32,GP32:GU32,GH32:GM32))

but its still not going through

Well the first way should still work and is the best option if you apply it to specific cells.

Otherwise as the range is not continous in the average statement it becomes more complicated but is still an array, shft ctrl enter

=AVERAGE(if(iserror(HF32:HK32),"",HF32:HK32),if(iserror(GX32:HC32),"",GX32:HC32),if(iserror(GP32:GU32),"",GP32:GU32),if(iserror(GH32:GM32),"",GH32:GM32))

Let me know if it works or post the sheet

regards

Dav