AVERAGEIF

S

Sasikiran

Dear,

I am struggling with AVERAGEIF formula which calculates the average of data
of one location without including 0 values.

In the below example, would require a formula to calculate the average of
the data which corresponds for MEXICO excluding 0 values.

A1 B1
MEXICO 5
CHICAGO 89
TEXAS 10
MEXICO 0
TEXAS 45
MEXICO 15
CHICAGO 0
TEXAS 15
MEXICO 0

Please help..
 
J

Jacob Skaria

Try AVERAGEIFS()

or the array formula
=AVERAGE(IF((A1:A10="Mexico")*(B1:B10>0),B1:B10))
 
R

Roger Govier

Hi

Because you have dual criteria, Mexico and >0 you will need to use
Sumproduct

=SUMPRODUCT((A1:A6="Mexico")*(B1:B6>0)*B1:B6)/
SUMPRODUCT((A1:A6="Mexico")*(B1:B6>0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MAX IF 2
Averageif Calcs 6
AverageIF 6
How to refer to a range except one cell 4
Ignoring Blank cells 9
AverageIF 1
AVERAGEIF FORMULA EXCEL 2007 1
Averageif puzzle 5

Top