*** HeLp with Array formula

D

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

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,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}))
 
D

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top