… and an alternative.
In this discussion at Eng-Tips I recommended the use of the DMAX() function to extract maximum values from data grouped with different text labels. In the process I needed to adjust the selection criterion to generate the correct results. DMAX requires a criteria range of at least two rows and one or more columns. In the simplest case the criterion consists of a column header with the criterion underneath; for instance:
=DMAX(datarange, 2, criteria)
Where criteria is a two row range containing:
First Name
Fred
Will return the maximum value from Column 2 from any row with the text “Fred” in the column headed “First Name”.
Unfortunatly this will not always work as expected, and in the example in the Eng-Tips discussion a criterion of M1 was also returning values from rows with labels M11, M12 etc.
The correct way to specify the criterion is to enter:
=”=M1″
which will appear as:
=M1
An alternative is to replace the DMAX function with an array function that will do the same job:
=MAX(IF(EXACT($C$7:$C$12928,U7),$E$7:$E$12928,-1000000))
In this case the labels are in Column C, the criteria are in Column U, and the data from which the maximum values are required are in column E. The final negative number must be less than the most negative value in the datarange.
This formula must be entered as an array function (by pressing ctrl-shift-enter). The screenshot below shows an example.
Array Formula as an alternative to DMax(), click to see full size
-
The criteria occupy only one cell, so may be placed in a list, next to the formula output
-
The criteria consist only of the text, without an equals sign
Eng-Tips members may download an example spreadsheet from the link.
