CODEWITHSUNDEEP
excelexcel-formula
Andrew Bartlett
Andrew Bartlett

Reputation: 5

Using Index to return max of lists

Any help here would be greatly received. I have a list of locations and a list of faults as thus:

Controller   Error
A            1
B            1
A            2
A            2
B            3

This is for many thousands of faults, what I need is a formula to return the name of the most occurring error for a given controller using excel.

Upvotes: 0

Views: 35

Answers (2)

elzell
elzell

Reputation: 2306

This should work:

=MODE(IF(A2:A6="A";B2:B6))

as an array formula (press Strg+Enter instead of Enter). Instead of "A" you can use a reference to a cell containing the controller you want to select. Be aware that in your minimal example this formula doesn't work for controller B because MODE apparently needs at least three values.

Upvotes: 0

XOR LX
XOR LX

Reputation: 7762

With that table in A1:B6 (with headers in row 1) and your choice of Controller, e.g. "B", in C1, array formula**:

=INDEX(B$2:B$6,MODE(IF(A$2:A$6=C1,MATCH(B$2:B$6,B$2:B$6,{0,0}))))

If, for the chosen Controller, no one Error is more frequent than any other, then, of those Errors sharing the highest frequency, that which occurs earliest in the list will be returned.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Upvotes: 2

Related Questions