… 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.
Newton Excel Bach, not (just) an Excel Blog 
I don’t know why D-functions work this way, the criteria “M1” and “M1*” are identical unless “transition formula evaluation” is checked. On the flip side they do have a speed advantage. A trick to propagate these formulas is to set up a data table with a column index, in the example:
U6=”Member Label”, V7=DMAX(C6:E999,3,U6:U7)
Select U7:V99 and choose Data|Table, Column input cell: U7, OK.
LikeLike
Try LARGE in place of MAX. U dont need the -100000000000 value anymore.
LikeLike
Thanks for the suggestion SirLansALot.
The funny thing is, I can’t now work out the reason for the -100000000000 being there. Probably because without it, if there is no match the function returns 0, rather than an error, which could be misleading, but in that case this would be better:
=MAX(IF(EXACT($C$7:$C$12928,U7),$E$7:$E$12928,NA())) which returns #NA! if there is no match.
Using LARGE you get a #NUM! error if there is no match, so both of those alternatives seem OK.
Of course using LARGE does also allow you to retuen values other than the maximum, which could also be useful.
LikeLike
Pingback: [ excel ] max date depends on one column | dsin