*** HeLp with Array formula

[Donna]

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

 

[T. Valko]

{=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,A11),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,A11),{1,2,3,4,5}))

 

[Donna]

thanks T,
so I want to get the average of the first five cells that hold a value in a
range of cells then I would leave out the word Small? I don't want it to be
the five smallest, just the first five that actuall have a value , like this?
=AVERAGE((K6:K19,A11),{1,2,3,4,5}))

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Average of bottom 5 (not 1st 5) from multiple sheets.
No formulas needed.
http://www.mediafire.com/file/im4immgyzja/03_11_10a.xlsx
Pdf preview:
http://www.mediafire.com/file/yi1uuzdgwdd/03_11_10a.pdf

 

[T. Valko]

I want to get the average of the first five cells that

hold a value in a range of cells then I would leave
out the word Small?

You could do that with values on separate sheets but it depends on whether
the values to average are all in a contiguous range (on each sheet). Are
there any empty cells *within* each range?

How about posting an example of your data and tell us what values should be
averaged? Post *real* data and the *real* location(s) of the data. Don't
make up some hypothetical example!!!