average with multiple criteria including OR

Hi all,

I have a dataset and I'm focusing on two of the columns, Trial and TaskError. I want to calculate the mean TaskError when Trial = 1 and TaskError is either a 1 or a 0. TaskError can also be -99 (as in missing) or -88 (some other criteria).

I tried AVERAGEIFS but found I couldn't put the OR criteria in.
Tried AVERAGE(IF((TRIALCELLS = 1)*((TASKERRORCELLS = 1)+(TASKERRORCELLS = 0)), TASKERRORCELLS)) and ctrl+shift+enter to get the curly bracets based on this post (https://www.excelforum.com/excel-for...statement.html) but I got an #N/A error.

Any help would be much appreciated as this is for around 25 people each with 5 trial types I need to do it for, and this then gets fed into a larger matlab script to do other stuff.

Thanks!!

Hopefully there should be an example attached...

I tried this within my actual dataset (not the example attached) and got 0 as the answer, but when I entered it in manually I got a different answer. Can I ask what the {} do in the formula?

The {0,1} used in conjunction with the SUM(...) is an OR condition so coercing SUMIF to execute twice, once for 0 and then 1: same for the COUNTIF.

Works fine on your posted file.

Originally Posted by xlnitwit

Hi,

Perhaps this
=SUM(SUMIFS(TASKERRORCELLS,TRIALCELLS ,1,TASKERRORCELLS,{0,1}))/SUM(COUNTIFS(TRIALCELLS ,1,TASKERRORCELLS,{0,1}))
normally entered.

Thanks for this, it does indeed work! My error -- I was trying it on trial type 4 but forgot to alter the formula to reflect that.

Thanks again!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.