excel - Calculate the average but ignore the error -

i have 3 columns of data (each contains 1 control's result). there cells in these 3 columns don't contain data ("absence of data" or "error"), when calculate averages, type of control #value.

i've tried use aggregate function ignore error have criteria choose different controls' result , calculate average separately. example:

apple1 | length | 0.5 apple1 | weight | 0.8 apple1 | width  | 0.6 apple2 | length | 0.5 apple2 | weight | absence of data apple2 | width  | 0.6 apple3 | length | error apple3 | weight | 0.8 apple3 | width  | 0.6 

and here formulas:

=aggregate(1,6,$c:$c,$b:$b,"length")) 

well i'd achieve averageifs function failed.

an array formula¹ can discard errors , blanks should not using full column references or calculating many irrelevant cells.

=average(if(b1:b9="length", if(isnumber(c1:c9), c1:c9))) 

this how done before averageifs function came along².

---

¹ _{array formulas need finalized ctrl+shift+enter↵. if entered correctly, excel wrap formula in braces (e.g. { , }). not type braces in yourself. once entered first cell correctly, can filled or copied down or right other formula. try , reduce full-column references ranges more closely representing extents of actual data. array formulas chew calculation cycles logarithmically practise narrow referenced ranges minimum. see guidelines , examples of array formulas more information.}

² _{hey! off lawn! damn kids (mutter, mutter...)}