How about:

=SUM(A1:A5)/COUNTIF(A1:A5,"<"&0)

Adjust the range to match your thousands of values.

Charles Deng wrote:

Hi All:

Is there anyone who can tell me how to calculate average
of valid values?

I have a huge data set with 1 to 6 valid values and 0 as
missing. I need to calculate average of only valid values
(1 to 6) for each record but to exclude 0 (missing). For
exmple,

1, 3, 3, 0, 5

The average for this record should be 3 because 0 is
missing value and should not be included. But when I use
Excel built in function Average, the result is 2.4.

Any help will be very very appreciated.

Charles

--

Dave Peterson