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

Use AVERAGEIFS

=AVERAGEIFS(B1:B9,A1:A9,"MEXICO",B1:B9,"0")

--

HTH

Bob

"Sasikiran" wrote in message
...
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..

Try AVERAGEIFS()

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

--
Jacob (MVP - Excel)


"Sasikiran" wrote:

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

Hi

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

=SUMPRODUCT((A1:A6="Mexico")*(B1:B60)*B1:B6)/
SUMPRODUCT((A1:A6="Mexico")*(B1:B60))
--
Regards
Roger Govier

Sasikiran wrote:
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..

Try this..

=AVERAGEIFS(B1:B10,A1:A10,"Mexico",B1:B10,"0")

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Try AVERAGEIFS()

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

--
Jacob (MVP - Excel)


"Sasikiran" wrote:

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