Average number excluding 0 and blanks with text criteria
 

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

Average number excluding 0 and blanks with text criteria
 

Hi,

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

if this helps please click yes thanks

"Pumpkin Pie" wrote:

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

Average number excluding 0 and blanks with text criteria
 

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
--
When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.


"Pumpkin Pie" wrote:

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

Average number excluding 0 and blanks with text criteria
 

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

--
Gary''s Student - gsnu200909


"Pumpkin Pie" wrote:

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

Average number excluding 0 and blanks with text criteria
 

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.
--
David Biddulph

"Gary''s Student" wrote in message
...
=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<0))

--
Gary''s Student - gsnu200909


"Pumpkin Pie" wrote:

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

Average number excluding 0 and blanks with text criteria
 

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" wrote:

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.
--
David Biddulph

"Gary''s Student" wrote in message
...
=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<0))

--
Gary''s Student - gsnu200909


"Pumpkin Pie" wrote:

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



.

Average number excluding 0 and blanks with text criteria
 

Glad it worked.
--
David Biddulph

"Pumpkin Pie" wrote in message
...
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" wrote:

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.
--
David Biddulph

"Gary''s Student" wrote in
message
...
=SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726))/SUMPRODUCT(--(A1:A726="Manchester"),--(M1:M726<0))

--
Gary''s Student - gsnu200909


"Pumpkin Pie" wrote:

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



.