How-to automatically select and chart the highest 10 totals?
 

I have a list of totals, around 25. I only want to chart a selection of
these totals- only the top 10.

I'm creating this spreadsheet for a group of non-techie school
counsellors. Can this whole process of sorting be automated? I know how
to make the chart, I'm just stuck on where do i get the top ten list.


Thank you so much for reading! Mutual help fourms and communities are
awesome!!!

keith


--
hopeace
------------------------------------------------------------------------
hopeace's Profile: http://www.excelforum.com/member.php...o&userid=28009
View this thread: http://www.excelforum.com/showthread...hreadid=475930

try this formula:

=LARGE($A$1:$A$25,1) will return the largest value in your range
=LARGE($A$1:$A$25,2) will return the second largest value in your
range and so on up to
=LARGE($A$1:$A$25,10) for the tenth largest value.

NOTE: ties will be repeated as many times as they appear. e.g.
1,2,3,3 will show 3 as largest AND second largest, 2 will be third
largest.

Plot the results of the formulas.

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475930

Brillant! Thanks for your help. A very good day to you!

keith


--
hopeace
------------------------------------------------------------------------
hopeace's Profile: http://www.excelforum.com/member.php...o&userid=28009
View this thread: http://www.excelforum.com/showthread...hreadid=475930

set of a set of cells with
=large(range,1)
to
=large(range,10)
this will give you the top ten in order

"hopeace" wrote:


I have a list of totals, around 25. I only want to chart a selection of
these totals- only the top 10.

I'm creating this spreadsheet for a group of non-techie school
counsellors. Can this whole process of sorting be automated? I know how
to make the chart, I'm just stuck on where do i get the top ten list.


Thank you so much for reading! Mutual help fourms and communities are
awesome!!!

keith


--
hopeace
------------------------------------------------------------------------
hopeace's Profile: http://www.excelforum.com/member.php...o&userid=28009
View this thread: http://www.excelforum.com/showthread...hreadid=475930

I'm glad it worked for you. Thanks for the feedback, it is always
appreciated.

Cheers!


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475930

Creating the formula for the top ten worked great! New issue:

How do I chose the aligned category or title of each of the chosen top
ten, so that I can include it in the chart I will make?

=LARGE($A$23:$A$57,1) through to =LARGE($A$23:$A$57,10) is what i
used.

The titles are in C23:C57

Thanks!!!

keith


--
hopeace
------------------------------------------------------------------------
hopeace's Profile: http://www.excelforum.com/member.php...o&userid=28009
View this thread: http://www.excelforum.com/showthread...hreadid=475930

Another approach you could use, which I think would also solve your
new question, is to create your chart as normal, but then apply an
AutoFilter to your data and use the filter option Top Ten.
If you want the data sorted, you could do that too.
hth
ScottO

"hopeace"
wrote in message
...
|
| Creating the formula for the top ten worked great! New issue:
|
| How do I chose the aligned category or title of each of the chosen
top
| ten, so that I can include it in the chart I will make?
|
| =LARGE($A$23:$A$57,1) through to =LARGE($A$23:$A$57,10) is what i
| used.
|
| The titles are in C23:C57
|
| Thanks!!!
|
| keith
|
|
| --
| hopeace
| -------------------------------------------------------------------
-----
| hopeace's Profile:
http://www.excelforum.com/member.php...o&userid=28009
| View this thread:
http://www.excelforum.com/showthread...hreadid=475930
|

You may also want to try a VLOOKUP. In the column next to your LARGE
formulas (I'll assume these are in D1:D10), enter this:

E1 =VLOOKUP(D1,$A$23:$C$57,3,0) and copy this down through E10.

Set this as the range (E1:E10) for your chart titles.

Good Luck.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475930

Well, I tried swatspOp's suggestion (thanks by the way!) and what
happened is where the numbers came back in the D1:D10 (large formula)
column as the same number, then the VLOOKUP just repeated the same
title over and over.

Example:

7....1...low income/unemployed
4....2....depression
2....3....family death
2....4....family death
2....5....family death
2....6....family death
1....7....separation/divorce
1....8....separation/divorce
1....9....separation/divorce
1....10....separation/divorce

The first column is the sort of LARGE formula: =LARGE($A$23:$A$57,1).
The second column is just tex to see the visual arangement of top ten
issues.
The third column is the formula: =VLOOKUP(A115,$A$23:$C$57,3,0).

This may be solvable, but I'm going to try scottO's suggestion as
well,

I'll be back...


--
hopeace
------------------------------------------------------------------------
hopeace's Profile: http://www.excelforum.com/member.php...o&userid=28009
View this thread: http://www.excelforum.com/showthread...hreadid=475930

Well, I cant get it to work. The Top ten function comes back with the
top ten numerals, by range. In other words it chooses the top numbers:
such as 7,4,2,1 and lists all of those numbers (19 of them) Rather than
choose only 10.

However, I've decided to stick with this, and rename the chart, as I
essentially wanted to remove any issue that had '0' students attached
to it.

Thanks for everyone's help!

keith


--
hopeace
------------------------------------------------------------------------
hopeace's Profile: http://www.excelforum.com/member.php...o&userid=28009
View this thread: http://www.excelforum.com/showthread...hreadid=475930