In that case you should change the 1000 in all four places to
something that is bigger than the maximum number of alarm types/rows.


On Tue, 5 Aug 2008 07:55:00 -0700, computers hate me
wrote:


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
"Lars-Åke Aspelin" wrote:

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 ??


On Tue, 05 Aug 2008 14:20:01 GMT, Lars-Åke Aspelin
wrote:

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

Lars-Åke

On Tue, 5 Aug 2008 07:03:02 -0700, computers hate me
wrote:

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:D$2000+ROW(A$2:A$2000)/1000,
MATCH(LARGE('Bf 4 Alarms'!D$2:D$2000+ROW(A$2:A$2000)/1000,ROW()),'Bf 4
Alarms'!D$2:D$2000+ROW(A$2:A$2000)/1000,0)),'Bf 4
Alarms'!D$2:D$2000+ROW(A$2:A$2000)/1000,0))





"Lars-Åke Aspelin" wrote:

On Tue, 5 Aug 2008 05:36:01 -0700, Bertha needs help
wrote:

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$7 00+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