AVERAGEIF
in [General]
|
From: Sasikiran on 5 May 2010 05:17 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..
From: Bob Phillips on 5 May 2010 05:42 Use AVERAGEIFS =AVERAGEIFS(B1:B9,A1:A9,"MEXICO",B1:B9,">0") -- HTH Bob "Sasikiran" <Sasikiran(a)discussions.microsoft.com> wrote in message news:0C92CE70-0434-46C2-B8D8-7F7C7C9DD525(a)microsoft.com... > 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.. > > > > >
From: Jacob Skaria on 5 May 2010 05:43 Try AVERAGEIFS() or the array formula =AVERAGE(IF((A1:A10="Mexico")*(B1:B10>0),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.. > > > > >
From: Roger Govier on 5 May 2010 05:46 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)) -- 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.. > > > > >
From: Jacob Skaria on 5 May 2010 05:56 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:B10>0),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.. > > > > > > > > > >
|
Pages: 1 Prev: Autofill Next: You cannot choose a connection file that is incompatible |