Averageifs

[johns99]

Having issues with the below formula

=AVERAGEIFS($CY$6:$CY$7808,$W$6:$W$7808,"x",$W$6:$W$7808,"+")

I'm trying to get an average for anything in column W with a "x" or "+"

any thoughts on this?

thanks,

John

 

[gsistek]

All argumements in any ...IFS function need to be met (think AND). In order to use OR logic, multiple functions are often needed. AVERAGEIFS is a bit trickier since you can't simply add multiple ...IFS functions together and call it a day (like you can when using SUMIFS or COUNTIFS). Try the following:
=(SUMIFS($CY$6:$CY$7808,$W$6:$W$7808,"x")+SUMIFS($CY$6:$CY$7808,$W$6:$W$7808,"+"))/(COUNTIFS($W$6:$W$7808,"x")+COUNTIFS($W$6:$W$7808,"+"))

 

[XOR LX]

Hi.

You could either use an array formula**:

=AVERAGE(IF($W$6:$W$7808={"x","+"},$CY$6:$CY$7808))

Or a longer, though non-array formula:

=SUM(SUMIF($W$6:$W$7808,{"x","+"},$CY$6:$CY$7808))/SUM(COUNTIF($W$6:$W$7808,{"x","+"}))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[johns99]

First I want to thank you both for your responses as they are very helpful...

When I use the following formula

{=AVERAGE(IF($W$6:$W$7808={"x","+"},$CY$6:$CY$7808))}

this returns the answer I was looking for, which is 7.26%...I tested by manually pulling the numbers out of the column and averaging

When I use:
=(SUMIFS($CY$6:$CY$7808,$W$6:$W$7808,"x")+SUMIFS($CY$6:$CY$7808,$W$6:$W$7808,"+"))/(COUNTIFS($W$6:$W$7808,"x")+COUNTIFS($W$6:$W$7808,"+"))
and
=SUM(SUMIF($W$6:$W$7808,{"x","+"},$CY$6:$CY$7808))/SUM(COUNTIF($W$6:$W$7808,{"x","+"}))

I'm getting the same answer, however it is different from the very first answer of 7.26%...it's actually 3.88%

do either of you know why?

thanks in advance.

 

[XOR LX]

I cannot think of any reason why those formulas should give different results.

Regards

 

[johns99]

I think it may have something to do with some of the cells being blank

 

[johns99]

So while this formula

{=AVERAGE(IF($W$6:$W$7808={"x","+"},$CY$6:$CY$7808))}

Is taking a total average of any cell with a "x" or "+" in it, the other two are actually dividing the sum and the count...which is why they are coming out identical.

 

[johns99]

Lastly, I've tried to use the similar formula on different criteria....

{=AVERAGE(IF($CQ$6:$CQ$7808={"P6","M5"},$CY$6:$CY$7808))}

But this seems to be coming out #N/A....any idea why this would occur?

Again, your help is much appreciated. thanks.

 

[gsistek]

The only thought I have is that you have 'x's and/or '+'s in column W that have blanks in the corresponding row in column CY. The approach that had the correct answer skips the blanks, whereas the other two formulas count the number of 'x's and '+'s essentially treating the blanks as zeros. If I were to guess, I'd say half of the 'x's and '+'s have blank cells assosiated with them, since the answers were different by a factor of 2.

 

[barry houdini]

Are the blanks completely blank cells or a blank returned by a formula?

If it's the latter then, yes, that can affect the result - you could also get different results if any of the numbers in $CY$6:$CY$7808 are text-formatted