Averaging with zeros NOT
 

{=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))}

Above is a formula that succesfully averages what I want. However
sometime the Prov!range r2:R2978 contains zeros.

How can I eliminate those zeros from the average?

The first part of the formula with the cell reference is looking up and
match a name before performin the average..

TIA

Tom

=AVERAGE(IF((Prov!$A$2:$A$2978=$A2)*(Prov!$R$2:$R$ 2978<0),Prov!$R$2:$R$2978
))

still an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tom" wrote in message
ups.com...
{=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))}

Above is a formula that succesfully averages what I want. However
sometime the Prov!range r2:R2978 contains zeros.

How can I eliminate those zeros from the average?

The first part of the formula with the cell reference is looking up and
match a name before performin the average..

TIA

Tom

Add another if statement right before you do the average:
=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,IF(Prov!$R$2:$R$ 2978=0,"",Prov!$R$2:$R$2978),""))

--
Regards,

Dave


"Tom" wrote:

{=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))}

Above is a formula that succesfully averages what I want. However
sometime the Prov!range r2:R2978 contains zeros.

How can I eliminate those zeros from the average?

The first part of the formula with the cell reference is looking up and
match a name before performin the average..

TIA

Tom

Hi!

Try this:

Array entered:

=AVERAGE(IF((Prov!$A$2:$A$2978=$A2)*(Prov!$R$2:$R$ 2978<0),Prov!$R$2:$R$2978))

Biff

"Tom" wrote in message
ups.com...
{=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))}

Above is a formula that succesfully averages what I want. However
sometime the Prov!range r2:R2978 contains zeros.

How can I eliminate those zeros from the average?

The first part of the formula with the cell reference is looking up and
match a name before performin the average..

TIA

Tom

You could create another column of data that read's the column you wish
to average like this {=if(A2=0,"",A2). This will effectively replace
the zeros with blanks, which are not included as zero values in an
average. Then you can change the column you wish to average with the
new column.

There'd be a myriad of more complicated and VBA-involved solutions, but
this is quick and easy.


--
Excel_Geek
------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=396927

Tom,

=SUMIF(A2:A2978,A2,R2:R2978)/SUMPRODUCT((A2:A2978=A2)*(R2:R2978<0))

Array-entering not necessary.
--
Earl Kiosterud
www.smokeylake.com

"Tom" wrote in message
ups.com...
{=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$29 78))}

Above is a formula that succesfully averages what I want. However
sometime the Prov!range r2:R2978 contains zeros.

How can I eliminate those zeros from the average?

The first part of the formula with the cell reference is looking up and
match a name before performin the average..

TIA

Tom