Array problem with Average

Hi All-
I am trying to use an array formula to average certain numbers in one column based on criteria from another column. However, I am generating incorrect results. I cannot see why it's not working. From what I can tell, the formula is adding up the correct cells but the dividing by the total number of cells to get the average (does that make sense).

This is the formula I wrote: {=AVERAGE(IF($F$4:$F$67="MM",$N$4:$N$67,0))}, and it's in the attached spreadsheet in cell N70. I want Excel to look at the numbers in column N and then give me an average of the numbers where the corresponding cell in column F has "MM" in it. Is that possible?

I was able to create a rather convoluted method using a COUNTIF formula and a conditional sum to generate the average, but I want to try to figure out why the array formula is not working. Thanks for whatever help you can provide, and please let me know what additional questions you might have.

You're adding 0 to the Array for each entry in F that is not MM - 0's will affect your AVERAGE.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

used over larger ranges the latter would be more efficient

(XL2007+ we'd use AVERAGEIF)

Don't include the 0 in the FALSE parameter,,, it ends up including 0's in the averaging where column F is non "MM"

E.g

=AVERAGE(IF($F$4:$F$67="MM",$N$4:$N$67))

Thanks so much guys! Worked perfectly. I saw that "0" there and wondered what it was doing but couldn't find an adequate explanation (probably because I didn't know what to call it as part of the formula).

You've save me a lot of trouble, and I'll be tipping your scales momentarily!

I wish I had Excel 2007. I can get it through my wife's company for about $45, but it's not approved by MY company for use on our machines...not that they'd be able to figure it out. Plus, I don't know if I want to deal with folks with downlevel versions having to access my spreadsheet. But I might just get it anyway!