I have two questions:
1. how to extract 5 or 10 maximum(bigger) values from a column range ?
2.how to extract 5 or 10 maximum (bigger) values from a column range basing
on another column criteria, say in col A-contaings group code A,B,C....in col
B- the values.I need 10 maximum values of group B in col C .

For the largest n:

=LARGE(B$1:B$20,ROWS($1:1)

If you want the 10 largest values then just copy the formula down 10 rows.

For the largest n based on a condition:

Array entered:

=LARGE(IF(A$1:A$20="A",B$1:B$20),ROWS($1:1))

If you want the 10 largest values then just copy the formula down 10 rows.
If there aren't 10 values that meet the condition you'll get #NUM! errors.

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
I have two questions:
1. how to extract 5 or 10 maximum(bigger) values from a column range ?
2.how to extract 5 or 10 maximum (bigger) values from a column range
basing
on another column criteria, say in col A-contaings group code A,B,C....in
col
B- the values.I need 10 maximum values of group B in col C .

Thank you Biff,I learned a lot from you .In these days,I observed my posts
are related to Rows,Row,and many more array functions.I would like to learn
these functions utilisation in broader sense.In excel books I find one line
example each for these functions, where as you use these functs.more
dynamically.

"Biff" wrote:

For the largest n:

=LARGE(B$1:B$20,ROWS($1:1)

If you want the 10 largest values then just copy the formula down 10 rows.

For the largest n based on a condition:

Array entered:

=LARGE(IF(A$1:A$20="A",B$1:B$20),ROWS($1:1))

If you want the 10 largest values then just copy the formula down 10 rows.
If there aren't 10 values that meet the condition you'll get #NUM! errors.

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
I have two questions:
1. how to extract 5 or 10 maximum(bigger) values from a column range ?
2.how to extract 5 or 10 maximum (bigger) values from a column range
basing
on another column criteria, say in col A-contaings group code A,B,C....in
col
B- the values.I need 10 maximum values of group B in col C .

You're welcome!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
Thank you Biff,I learned a lot from you .In these days,I observed my posts
are related to Rows,Row,and many more array functions.I would like to
learn
these functions utilisation in broader sense.In excel books I find one
line
example each for these functions, where as you use these functs.more
dynamically.

"Biff" wrote:

For the largest n:

=LARGE(B$1:B$20,ROWS($1:1)

If you want the 10 largest values then just copy the formula down 10
rows.

For the largest n based on a condition:

Array entered:

=LARGE(IF(A$1:A$20="A",B$1:B$20),ROWS($1:1))

If you want the 10 largest values then just copy the formula down 10
rows.
If there aren't 10 values that meet the condition you'll get #NUM!
errors.

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
I have two questions:
1. how to extract 5 or 10 maximum(bigger) values from a column range ?
2.how to extract 5 or 10 maximum (bigger) values from a column range
basing
on another column criteria, say in col A-contaings group code
A,B,C....in
col
B- the values.I need 10 maximum values of group B in col C .

I found this post when searching for an answer to my own challenge. I tried
the solution you posted, but couldn't get it to work for me. I was hoping you
could help.

I have a database of monthly sales by type, department, customer, and
division. This database will be updated monthly. My challenge is to create an
executive dashboard that will update automatically when the new data is
uploaded. Formulas can be complex, but must be dynamic so that they do not
require any input from the user when the new data is uploaded.

The items I am struggling with are the top 10 lists. They want to see the
top 10 customers -- easy enough, but then they want to see the top 10
customers within each division (select the top 10 customers where division =
"A"), then they want to see the top 10 customersfor each of the product types
within each division (select top 10 cusotmers where division = "A" and type =
"B"), etc. I can't figure out how to use the LARGE function with multiple
criteria. I think I should imbed an IF statement (or two), but can's seem to
make it work.

Based on your response to Tungana, I am convinced you are the man for the
job. Thanks for your help.



"Biff" wrote:

You're welcome!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
Thank you Biff,I learned a lot from you .In these days,I observed my posts
are related to Rows,Row,and many more array functions.I would like to
learn
these functions utilisation in broader sense.In excel books I find one
line
example each for these functions, where as you use these functs.more
dynamically.

"Biff" wrote:

For the largest n:

=LARGE(B$1:B$20,ROWS($1:1)

If you want the 10 largest values then just copy the formula down 10
rows.

For the largest n based on a condition:

Array entered:

=LARGE(IF(A$1:A$20="A",B$1:B$20),ROWS($1:1))

If you want the 10 largest values then just copy the formula down 10
rows.
If there aren't 10 values that meet the condition you'll get #NUM!
errors.

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
I have two questions:
1. how to extract 5 or 10 maximum(bigger) values from a column range ?
2.how to extract 5 or 10 maximum (bigger) values from a column range
basing
on another column criteria, say in col A-contaings group code
A,B,C....in
col
B- the values.I need 10 maximum values of group B in col C .

Hi!

I almost missed your post! I was "cleaning up" and noticed a new reply to
the thread.

You can use this technique:

=LARGE(IF((A1:A100="division")*(B1:B100="product") ,F1:F100),ROWS($1:1))

You can add more conditions in the same manner:

Division by Product by Salesperson:

=LARGE(IF((A1:A100="division")*(B1:B100="product") *(C1:C100="Salesperson"),F1:F100),ROWS($1:1))

These are array formulas. They need to be entered using the key combination
of CTRL,SHIFT,ENTER (not just ENTER):

http://cpearson.com/excel/array.htm

My challenge is to create an executive dashboard that will
update automatically when the new data is uploaded.

Sounds like you want to use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

Biff

"Adeline" wrote in message
...
I found this post when searching for an answer to my own challenge. I tried
the solution you posted, but couldn't get it to work for me. I was hoping
you
could help.

I have a database of monthly sales by type, department, customer, and
division. This database will be updated monthly. My challenge is to create
an
executive dashboard that will update automatically when the new data is
uploaded. Formulas can be complex, but must be dynamic so that they do not
require any input from the user when the new data is uploaded.

The items I am struggling with are the top 10 lists. They want to see the
top 10 customers -- easy enough, but then they want to see the top 10
customers within each division (select the top 10 customers where division
=
"A"), then they want to see the top 10 customersfor each of the product
types
within each division (select top 10 cusotmers where division = "A" and
type =
"B"), etc. I can't figure out how to use the LARGE function with multiple
criteria. I think I should imbed an IF statement (or two), but can's seem
to
make it work.

Based on your response to Tungana, I am convinced you are the man for the
job. Thanks for your help.



"Biff" wrote:

You're welcome!

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
Thank you Biff,I learned a lot from you .In these days,I observed my
posts
are related to Rows,Row,and many more array functions.I would like to
learn
these functions utilisation in broader sense.In excel books I find one
line
example each for these functions, where as you use these functs.more
dynamically.

"Biff" wrote:

For the largest n:

=LARGE(B$1:B$20,ROWS($1:1)

If you want the 10 largest values then just copy the formula down 10
rows.

For the largest n based on a condition:

Array entered:

=LARGE(IF(A$1:A$20="A",B$1:B$20),ROWS($1:1))

If you want the 10 largest values then just copy the formula down 10
rows.
If there aren't 10 values that meet the condition you'll get #NUM!
errors.

Biff

"TUNGANA KURMA RAJU"
wrote
in
message ...
I have two questions:
1. how to extract 5 or 10 maximum(bigger) values from a column range
?
2.how to extract 5 or 10 maximum (bigger) values from a column range
basing
on another column criteria, say in col A-contaings group code
A,B,C....in
col
B- the values.I need 10 maximum values of group B in col C .