Average number excluding 0 and blanks with text criteria

[Pumpkin Pie]

Hello smart people

I have a list of survey names in A3:A726 and numbers in M3:M726.
Column M also contains 0 and blanks.

I am doing a summary table on the next sheet so I need a formula to show if
it is 'SurveyName="Manchester" then average 'Autumn 09'!M3:M726 (ignoring
zeros and blanks)

Any help would be greatly appreciated as this is beyond my knowledge.

Many thaks

Paula

 

[Eduardo]

Hi,

=AVERAGEIF(A3:A726,"=Manchester",M3:M726)

if this helps please click yes thanks

 

[Mike H]

Hi,

Or if your not using E2007 try this array formula

=AVERAGE(IF(A3:A726="Manchester",IF(M3:M726<>0,M3:M726)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Gary''s Student]

=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<>0))

 

[David Biddulph]

To be picky, I think you can live without the second set of double unary
minus.
I think you could change
=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<>0))
to
=SUMPRODUCT(--(A1:A726="Manchester"),M1:M726)/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<>0))

But your version has the advantage that if the "numbers" in column M are
text that looks like numbers it will still work, whereas the shorter version
wouldn't.
Conversely, however, if the "blanks" in column M are "formula blanks" such
as ="", the shorter version of the formula will work whereas the longer one
won't.

 

[Pumpkin Pie]

Thank you too all who replied.

David's shorter version worked brilliantly. The others didnt unfortunately
but it may be due to other factors in how the spreadsheets were set up with
other formula.

Thanks once again to you all.

Paula
x

 

[David Biddulph]

Glad it worked.
--
David Biddulph

Thank you too all who replied.

David's shorter version worked brilliantly. The others didnt unfortunately
but it may be due to other factors in how the spreadsheets were set up
with
other formula.

Thanks once again to you all.

Paula
x