Averaging with zeros NOT

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

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.

=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" <TesnoBay@comcast.net> wrote in message
news:1124387946.250329.45000@z14g2000cwz.googlegroups.com...
> {=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$2978))}
>
> 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$2978))}
>
> 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" <TesnoBay@comcast.net> wrote in message
news:1124387946.250329.45000@z14g2000cwz.googlegroups.com...
> {=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$2978))}
>
> 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
>

Tom,

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

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

"Tom" <TesnoBay@comcast.net> wrote in message
news:1124387946.250329.45000@z14g2000cwz.googlegroups.com...
> {=AVERAGE(IF(Prov!$A$2:$A$2978=$A2,Prov!$R$2:$R$2978))}
>
> 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
>