| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the top 10
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
Have a look in the help index for LARGE
-- Don Guillett Microsoft MVP Excel SalesAid Software "Bertha needs help" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
Hi Bertha,
Use 10 LARGE() functions. See HELP or details -- Kind regards, Niek Otten Microsoft MVP - Excel "Bertha needs help" wrote in message ... |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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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? "Niek Otten" wrote: Hi Bertha, Use 10 LARGE() functions. See HELP or details -- Kind regards, Niek Otten Microsoft MVP - Excel "Bertha needs help" wrote in message ... |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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
|
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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'!D2:D2000,ROW('Bf 4 Alarms'!A2)),'Bf 4 Alarms'!D2:D2000)) "Don Guillett" wrote: Try this. =INDEX(A:A,MATCH(LARGE(K:K,ROW(A1)),K:K)) -- Don Guillett Microsoft MVP Excel SalesAid Software "computers hate me" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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 On Tue, 5 Aug 2008 10:12:40 -0500, "Don Guillett" wrote: Try this. =INDEX(A:A,MATCH(LARGE(K:K,ROW(A1)),K:K)) |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the top 10
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding 1st,2nd,3rd etc | Excel Discussion (Misc queries) | |||
| Finding last used | Excel Worksheet Functions | |||
| Finding value | Excel Discussion (Misc queries) | |||
| finding the "end" | New Users to Excel | |||
| Finding Value | Excel Worksheet Functions | |||
![Bertha needs help[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
