Finding the top 10

[Bertha needs help]

I have a list of 700 alarms in column A then In column B i have a formula
that counts how many times each alarm went off.

In another sheet i want to make a chart that shows the top 10 alarms that
went off that day.

What formula could i use to find the top 10 values?

 

[Don Guillett]

Have a look in the help index for LARGE

 

[Niek Otten]

Hi Bertha,

Use 10 LARGE() functions.
See HELP or details

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a list of 700 alarms in column A then In column B i have a formula
| that counts how many times each alarm went off.
|
| In another sheet i want to make a chart that shows the top 10 alarms that
| went off that day.
|
| What formula could i use to find the top 10 values?

 

[computers hate me]

ok that works to find the top 10 largest values but now i want it to copy
over the 10 ten names of the alarm and the times it went off.

The names are on Sheet 1 column A and the number of times it went off is on
column D.

So, lets say i find the top score how do i do it so that it copies over that
score with its corresponding alarm over to Sheet 1 column B?

 

[Lars-Åke Aspelin]

I have a list of 700 alarms in column A then In column B i have a formula
that counts how many times each alarm went off.

In another sheet i want to make a chart that shows the top 10 alarms that
went off that day.

What formula could i use to find the top 10 values?

In cell A1 of the other sheet, put

=INDEX(Sheetq!A$1:A$700,MATCH(INDEX(Sheet1!B$1:B$700+ROW(A$1:A$700)/1000,
MATCH(LARGE(Sheet1!B$1:B$700+ROW(A$1:A$700)/1000,ROW()),
Sheet1!B$1:B$700+ROW(A$1:A$700)/1000,0)),Sheet1!B$1:B$700+ROW(A$1:A$700)/1000,0))

Note this formula is an array formula that has to be entered by
CTRL+SHIFT+ENTER rather than just ENTER.


In cell B1 of the other sheet, put

=LARGE(Sheet1!B$1:B$700,ROW())

Copy these formulas from A1:B1 down to A10:B10 to get the table of the
names of the 10 most frequent alarms and their respective frequencies.

Hope this helps / Lars-Åke

 

[Don Guillett]

try
=INDEX(A:A,MATCH(LARGE(d1:d21,4),d:d))

 

[computers hate me]

ok it gives me a NUM# Error
I dont understand the formula so i dont know how to subtitute my values into
it
this is the formula i used

=INDEX('Bf 4 Alarms'!A$2:A$2000,MATCH(INDEX('Bf 4
Alarms'!D$2$2000+ROW(A$2:A$2000)/1000,
MATCH(LARGE('Bf 4 Alarms'!D$2$2000+ROW(A$2:A$2000)/1000,ROW()),'Bf 4
Alarms'!D$2$2000+ROW(A$2:A$2000)/1000,0)),'Bf 4
Alarms'!D$2$2000+ROW(A$2:A$2000)/1000,0))

 

[Lars-Åke Aspelin]

Did you enter the formula as an array formula?
If you don't do that, you will get NUM# error.

Lars-Åke

 

[Lars-Åke Aspelin]

Also, this formula relies on that the range with alamrs is less than
1000 rows.
You said that you have 700 alarms, so why do you enter A$2:A$2000 ??

 

[computers hate me]

because right now there are only 700 but we will add more eventually and it
could add up to about 2000. So i wanted to set the formula so that if we add
more it will still work

 

[Don Guillett]

Try this.
=INDEX(A:A,MATCH(LARGE(K:K,ROW(A1)),K:K))

 

[computers hate me]

ok i tried it putting in my values and the only thing it returned to me was
the last value in column A
this is my formula
=INDEX('Bf 4 Alarms'!A2:A2000,MATCH(LARGE('Bf 4 Alarms'!D22000,ROW('Bf 4
Alarms'!A2)),'Bf 4 Alarms'!D22000))

 

[Lars-Åke Aspelin]

The problem with this formula is that, even if you set match_type to 0
(exact match), it will not work if there are cells i the K column that
have the same value.
And it seems possible that in this case there are more than one alarm
type/name that have the same number of times that they have went off.

Lars-Åke