extraction of max values from a range
 

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 .

extraction of max values from a range
 

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 .

extraction of max values from a range
 

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 .

extraction of max values from a range
 

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 .

extraction of max values from a range
 

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 .

extraction of max values from a range
 

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 .

extraction of max values from a range
 

Thanks, Biff. I am glad you noticed my post. I knew you would be able to
help. Can I ask a couple more quesitons to help with my understanding?

1. What is the purpose of this last bit of the formula "ROWS($1:1)"?
2. Why do you use the multipliation symbol "*" instead of an "&" to combine
the criteria?
3. Why doesn't the formula work when I change the references to absolute
references? But "doesn't work" I mean that the formula returns the largest
value in column F instead of the largest value that meet the specified
criteria (a=division, b=product ect)

Thanks so much for your help and thanks for doing this. I almost always find
the answers I need just by searching this forum.


"Biff" wrote:

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 .

extraction of max values from a range
 

What is the purpose of this last bit of the formula "ROWS($1:1)"?

That tells the Large function to return the nth value. Using the Rows
function is just a means of incrementing that value. For example, if you
want the top 10 values instead of writing 10 different formulas like this:

=LARGE(A1:A100,1)
=LARGE(A1:A100,2)
=LARGE(A1:A100,3)
etc

You need only write a single formula and copy it to 10 cells. The Rows
function will increment like this:

=LARGE(A$1:A$100,ROWS($1:1))
=LARGE(A$1:A$100,ROWS($1:2))
=LARGE(A$1:A$100,ROWS($1:3))
etc

Why do you use the multipliation symbol "*" instead of
an "&" to combine the criteria?

You don't want to "combine" the different criteria. Although you could, it
just adds unnecessary calculation steps to the formula.

Why doesn't the formula work when I change the references to absolute
references? But "doesn't work" I mean that the formula returns the largest
value in column F instead of the largest value that meet the specified
criteria (a=division, b=product ect)

That should have nothing to do with it. Using absolute refs just "locks"
ranges. If you drag copy the formula you want the refs to be absolute
(except for this part: ROWS($1:1))

Biff

"Adeline" wrote in message
...
Thanks, Biff. I am glad you noticed my post. I knew you would be able to
help. Can I ask a couple more quesitons to help with my understanding?

1. What is the purpose of this last bit of the formula "ROWS($1:1)"?
2. Why do you use the multipliation symbol "*" instead of an "&" to
combine
the criteria?
3. Why doesn't the formula work when I change the references to absolute
references? But "doesn't work" I mean that the formula returns the largest
value in column F instead of the largest value that meet the specified
criteria (a=division, b=product ect)

Thanks so much for your help and thanks for doing this. I almost always
find
the answers I need just by searching this forum.


"Biff" wrote:

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 .

extraction of max values from a range
 

Thanks! You have been a big help.

"Biff" wrote:

What is the purpose of this last bit of the formula "ROWS($1:1)"?

That tells the Large function to return the nth value. Using the Rows
function is just a means of incrementing that value. For example, if you
want the top 10 values instead of writing 10 different formulas like this:

=LARGE(A1:A100,1)
=LARGE(A1:A100,2)
=LARGE(A1:A100,3)
etc

You need only write a single formula and copy it to 10 cells. The Rows
function will increment like this:

=LARGE(A$1:A$100,ROWS($1:1))
=LARGE(A$1:A$100,ROWS($1:2))
=LARGE(A$1:A$100,ROWS($1:3))
etc

Why do you use the multipliation symbol "*" instead of
an "&" to combine the criteria?

You don't want to "combine" the different criteria. Although you could, it
just adds unnecessary calculation steps to the formula.

Why doesn't the formula work when I change the references to absolute
references? But "doesn't work" I mean that the formula returns the largest
value in column F instead of the largest value that meet the specified
criteria (a=division, b=product ect)

That should have nothing to do with it. Using absolute refs just "locks"
ranges. If you drag copy the formula you want the refs to be absolute
(except for this part: ROWS($1:1))

Biff

"Adeline" wrote in message
...
Thanks, Biff. I am glad you noticed my post. I knew you would be able to
help. Can I ask a couple more quesitons to help with my understanding?

1. What is the purpose of this last bit of the formula "ROWS($1:1)"?
2. Why do you use the multipliation symbol "*" instead of an "&" to
combine
the criteria?
3. Why doesn't the formula work when I change the references to absolute
references? But "doesn't work" I mean that the formula returns the largest
value in column F instead of the largest value that meet the specified
criteria (a=division, b=product ect)

Thanks so much for your help and thanks for doing this. I almost always
find
the answers I need just by searching this forum.


"Biff" wrote:

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 .

extraction of max values from a range
 

You're welcome!

Biff

"Adeline" wrote in message
...
Thanks! You have been a big help.

"Biff" wrote:

What is the purpose of this last bit of the formula "ROWS($1:1)"?

That tells the Large function to return the nth value. Using the Rows
function is just a means of incrementing that value. For example, if you
want the top 10 values instead of writing 10 different formulas like
this:

=LARGE(A1:A100,1)
=LARGE(A1:A100,2)
=LARGE(A1:A100,3)
etc

You need only write a single formula and copy it to 10 cells. The Rows
function will increment like this:

=LARGE(A$1:A$100,ROWS($1:1))
=LARGE(A$1:A$100,ROWS($1:2))
=LARGE(A$1:A$100,ROWS($1:3))
etc

Why do you use the multipliation symbol "*" instead of
an "&" to combine the criteria?

You don't want to "combine" the different criteria. Although you could,
it
just adds unnecessary calculation steps to the formula.

Why doesn't the formula work when I change the references to absolute
references? But "doesn't work" I mean that the formula returns the
largest
value in column F instead of the largest value that meet the specified
criteria (a=division, b=product ect)

That should have nothing to do with it. Using absolute refs just "locks"
ranges. If you drag copy the formula you want the refs to be absolute
(except for this part: ROWS($1:1))

Biff

"Adeline" wrote in message
...
Thanks, Biff. I am glad you noticed my post. I knew you would be able
to
help. Can I ask a couple more quesitons to help with my understanding?

1. What is the purpose of this last bit of the formula "ROWS($1:1)"?
2. Why do you use the multipliation symbol "*" instead of an "&" to
combine
the criteria?
3. Why doesn't the formula work when I change the references to
absolute
references? But "doesn't work" I mean that the formula returns the
largest
value in column F instead of the largest value that meet the specified
criteria (a=division, b=product ect)

Thanks so much for your help and thanks for doing this. I almost always
find
the answers I need just by searching this forum.


"Biff" wrote:

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 .