{=AVERAGE(SMALL(K7:K20,ROW($1:$5)))}
This formual works great to find an average for
the first five cells that have a value

Actually, what your formula is doing is getting the average of the 5 lowest
values in the range. If the 5 lowest values happen to be the first 5 values
in the range then that's just a coincidence.

want to look at cells from two different sheets
how would I write the formula.
{=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5))) }

SMALL will accept multiple area references *but* they all have to be on the
same sheet:

{=AVERAGE(SMALL((K6:K19,K19:K24,A1:D1),ROW($1:$5)) )}

At this moment I can't think of a way to do this with the data on separate
sheets. If you put all the data on the same sheet then it's simple.

Also, you can do this without using an array entered formula plus, it's more
robust than using the expression ROW($1:$5) which leaves the formula
vulnerable to row insertions.

=AVERAGE(SMALL((K6:K19,A1:D1),{1,2,3,4,5}))

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
{=AVERAGE(SMALL(K7:K20,ROW($1:$5)))}
This formual works great to find an average for the first five cells that
have a value, however If want to look at cells from two different sheets
how
would I write the formula.
I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get
a
#value! ( one of the sheets is Mar and one is Feb)
***Basically I need to get an average from the first five cells that have
values, But I need to look at cells in two different sheets.