*** HeLp with Array formula
 

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

*** HeLp with Array formula
 

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

*** HeLp with Array formula
 

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,A1:D1),{1,2,3,4,5}))

"T. Valko" wrote:

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


.

*** HeLp with Array formula
 

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

*** HeLp with Array formula
 

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!!!

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
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,A1:D1),{1,2,3,4,5}))

"T. Valko" wrote:

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


.