Average If

[tomandrobyn]

I currently have this array formula in use, which is great for giving m
an average...

={AVERAGE(IF($D$6:$D$63="Enhanced",M$6:M$63))}

My problem is with cells that have no information. Say, for example
that 3 cells match "enhanced" but only 2 of those have numbers. Th
blank cell is bringing my average down.

Any help would be greatly appreciated

 

[Frank Kabel]

Hi
if the cell is really blank AVERAGE shopuld ignore this cell

 

[JE McGimpsey]

One way (array-entered):

=AVERAGE(IF(($D$6:$D$63="Enhanced")*(M$6:M$63<>""),M$6:M$63))

 

[JE McGimpsey]

No.

In an array formula, M$6:M$63 will be coerced to 0.

 

[Frank Kabel]

Hi JE
good point, forgot about this

 

[tomandrobyn]

That's great, thanks alot! Works like a charm

 

[Jerry W. Lewis]

Which is easily handled with a slightly more complicated IF

={AVERAGE(IF(($D$6:$D$63="Enhanced")*ISNUMBER(M$6:M$63),M$6:M$63))}

Jerry