Average number excluding 0 and blanks with text criteria

  • Thread starter Thread starter Pumpkin Pie
  • Start date Start date
P

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
 
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
 
=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<>0))
 
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.
 
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
 
Glad it worked.
--
David Biddulph

Pumpkin Pie said:
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
Click to expand...
 
Back
Top