extraction of max values from a range

[Guest]

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 .

 

[Biff]

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

 

[Guest]

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]

You're welcome!

Biff

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.

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Thanks! You have been a big help.

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


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


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

 

[Biff]

You're welcome!

Biff

Thanks! You have been a big help.