@ > Home > Contents > Select an extreme record, decided by subordinate criteria Excel 97+

Task: 

Select a record from a set with same earliest "Date" by selecting the highest "Count". If this combination is still not unique, go further to smallest "Sum" and then to most "Errors".

        A     B      C      D     
  1 Date    Count  Sum   Errors  
  2 01.04.08  1     78      0     
  3 01.04.08  2     45      0     
  4 01.04.08  1     67      0     
  5 01.04.08  1     68      1     
  6 01.04.08  2     89      2     
  7 01.04.08  1     91      1     
  8 02.05.08  2     56      1     
  9 01.04.08  2     45      1     
 10 01.04.08  1     91      0     
 11 01.04.08  1     67      2     
 12                               
 13 Satz:     8  (in Zeile 9)

Solution (works only with numbers!):

B13: =MATCH(MIN(A2:A11-B2:B11%+C2:C11%%-D2:D11%%%),A2:A11-B2:B11%+C2:C11%%-D2:D11%%%,)

The formula has to be typed in as an array formula (by pressing Ctrl-Shift-Enter), otherwise it does not work. Do not forget the final "," for unsorted data.

How it works:

The highlighted parts show how long records have common extremes. D:D is the first one being unique.

MIN returns the earliest "Date" (MAX would do the latest, respectively). Since there is no unique one, be more precise by searching for its highest "Count" (-B2:B11%), by subtracting its column, %-added. Still not being unique, one could look for smallest "Sum" in between (+C2:C11%%), and thereafter for most "Errors" (-D2:D11%%%), if "Date"-"Count"-"Sum" were not unique yet. The more % are added, the more subordinate the criteria are. The Plus of % also depends on the scale of the numbers:

<=9 results into extra /10
<=99 results into extra %
<=999 results into extra %/10 or /1000
<=9999 results into extra %% etc.

- Since Excel is limited to 15 decimals, this delimits the usage of criteria. Here 4 criteria use 11 decimals.
- MAX (instead of MIN) means that all subsequent +/- have to be changed to -/+.
- The order of columns is free: C could also be subordinate to D, expressed by more % attached.
- MATCH also works with a transposed problem (rows instead of columns and vice versa).
- This example uses 4 criteria levels. It could be from 1 to 15 instead, depending on each of its used decimals.
- Replace a vector by a reference, and you will change the extreme to a value on each possible level. Hopefully such a record exists ...