Using Average function across multiple tabs, ignoring #NUM! and 0

[Jaice]

Hi Guys, wondering if somebody can assist me. I have 4 tabs of data (Name 1- Name 4), and im looking to get an average of the data (across all tabs) while ignoring any #NUM! or 0 values (to be inserted in corresponding tables in Sheet 1). If anyone has an idea as to the best way to do this, that would be greatly appreciated. Thanks

[martindwilson]

first fix the numder and div/0 errors the worksheet you have posted has no formulas in it

[Jaice]

first fix the numder and div/0 errors the worksheet you have posted has no formulas in it

The worksheet I have posted has been copy and pasted off another file (formulas were not copied over). The number and div/0 errors are where the errors in the real worksheet are

[Jaice]

Can anybody help me out with this please?

[Jaice]

Still having difficulties here. Struggling to find anything on the net, any assistance would be great!

[martindwilson]

=AVERAGE(IF(N(INDIRECT("'"&b2:b5&"'!d4"))<>0,N(INDIRECT("'"&b2:b5&"'!d4")))) where b2:b5 is a list of sheet names
the formula needs to be array entered with ctrl+shift+enter see sheet1 d4
but you need to fix any errors first
#NUM! #DIV/0! just wrap the formulas in iferror(your formula,0)

[Jaice]

=AVERAGE(IF(N(INDIRECT("'"&b2:b5&"'!d4"))<>0,N(INDIRECT("'"&b2:b5&"'!d4")))) where b2:b5 is a list of sheet names
the formula needs to be array entered with ctrl+shift+enter see sheet1 d4
but you need to fix any errors first
#NUM! #DIV/0! just wrap the formulas in iferror(your formula,0)

Awesome! Thanks very much