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