| Home |
| Search |
| Today's Posts |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the top five incidents
Sorry Leo!
I'm looking for 12,10,8,8,8 "Leo Heuser" wrote: "Danny Lewis" skrev i en meddelelse ... It is actually a little more complicated than I said. There are other columns in the table, but I didnt want it to seem more complicated. What I need is to pick up records with variables in two other columns that equal certain things. For example... Column A: Incident No B: Date C: Description D: Minutes 1 E: Minutes 2 F: Function (eg Engineering) G: Area I need to find the top 5 incidents in terms of minutes 1, where Function is x, and Area is y... There's a pickle for ya lol... Hi Danny Don't expect a solution, if you do not disclose all facts from the beginning :-) Try this array formula instead with Function in B2:B24 and Area in D2:D24 If x and y are text: =MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24)* ($B$2:$B$24="x")*($D$2:$D$24="y")) If x and y are numbers: =MAX(IF(COUNTIF($F$1:F1,$C$2:$C$24)=0,$C$2:$C$24)* ($B$2:$B$24=x)*($D$2:$D$24=y)) again to be entered with <Shift<Ctrl<Enter I'm beginning to wonder about your remark on not wanting duplicates in "Minute 1". If for instance you have the numbers 12,10,8,8,8,7,6 will your top 5 picks be 12,10,8,8,8 or 12,10,8,7,6 ? Leo Heuser "Leo Heuser" wrote: "Danny Lewis" skrev i en meddelelse ... Thanks Leo I tried yours but was a bit confused...any chance of being a bit clearer? Would be hugely appreciated - I did manage to write the formula so it brought up unique values only (is that what it's meant to do?) I got the impression, that you had duplicates in "Minute 1" and that you didn't want them in your list, so yes my formula brings up unique values only. How do you enter the minutes in "Minute 1"? As numbers 3, 6, 12, 7, 6 etc or as time 0:3, 0:6 etc. If you enter them as time, then select F2:F6 and give it the user- defined format [mm] to get a list of the top 5 minutes. Leo Heuser |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number of incidents of a month | Excel Worksheet Functions | |||
| filter errors depending on number of incidents | Excel Discussion (Misc queries) | |||
Threaded Mode
