=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