CODEWITHSUNDEEP
excelexcel-formulaaverage
Hiddenllyy
Hiddenllyy

Reputation: 179

Calculate the average but ignore the error

I have three columns of data (each contains one control's result). But there are some cells in these three columns which don't contain the data ("absence of data" or "Error"), so when I calculate the averages, some type of control are #VALUE.

I've tried to use AGGREGATE function to ignore the error but I have some criteria to choose the different controls' result and calculate the average separately. For 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 is my formulas:

=AGGREGATE(1,6,$C:$C,$B:$B,"length"))

Well I'd like to achieve is like an AVERAGEIFS function but failed.

Upvotes: 1

Views: 980

Answers (1)

user4039065
user4039065

Reputation:

An array formula¹ can discard the errors and blanks but you should not be using full column references or you will be calculating many irrelevant cells.

=AVERAGE(IF(B1:B9="Length", IF(ISNUMBER(C1:C9), C1:C9)))

This is how it was done before the AVERAGEIFS function came along².

if_div0

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

² Hey! Get off my lawn! Damn kids (mutter, mutter...)

Upvotes: 1

Related Questions