Averaging with zeros NOT

T

Tom

{=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
 
B

Bob Phillips

=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)
 
D

DaveB

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),""))
 
B

Biff

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
 
E

Excel_Geek

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

Earl Kiosterud

Tom,

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

Array-entering not necessary.
 
Top