Average, avoid column or cells where error

[hassan88]

hey all,untitled.jpg

I am trying to calculate averages for all the imported data, attached is to simplify explanation, but as some values are not valid (hach value, DIV/0!) the average is returning the same error. Is there a syntax to keep pasting all data as we have already done but when calculating averages to disregard the whole column that contain errors?

best wishes

[Richard Buttrey]

Hi,

Can't you just wrap the whole formula in an =IFERROR() function and specify "", or zero, or whatever text you want as the second parameter after your formula?

[hassan88]

Hi,
I have not done this before, can you help me with an example please.

Thank You.

[Richard Buttrey]

Hi

It would have helped if you had uploaded the workbook rather than a picture (as per the advice in the forum rules), pictures are rarely much use to us.

What I meant was, with the formula that is returning DIV/0! or indeed any other error result, put the formula in an =IFERROR() function.

i.e. =IFERROR(your_formula,"")

[hassan88]

many thanks,

will make a go on it.

[AlKey]

Assumes that your data in A1:A10

This is an array formula and must be entered with Ctrl+Shft+Enter combination

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).