We are often faced with the practical situation in which we need to return the entry from a certain column which corresponds to the maximum numerical value from another column subject to one or more conditions.
For example, from the table below:
we may wish to return the date (column C) which corresponds to the latest version (column B) for a given order number (column A), where by “latest” we mean “largest numerically”.
Last week I set readers the challenge which can be found here.
This one was perhaps a little less complex than ones I’d set in previous weeks, though of course it would still, in my opinion, fall within the boundaries of what I would deem “advanced Excel”.
It also demonstrates some techniques which we can apply to solving problems involving non-contiguous ranges, and in particular tell us which functions may be applicable to such set-ups.
Two good solutions received from John Jairo V and cyrilbrd (and Bill‘s was practically there as well, but for a small amendment – and the fact that I didn’t structure the question in full to begin with – sorry!).
We are often in a position where we wish to exclude certain values from an array of results before passing that array to another function.
For example, a common, practical situation is that of finding the minimum value from a range whilst excluding zeroes. This can be done in several ways, for example using an array formula:
=MIN(IF(A1:A10<>0,A1:A10))
or, if we have Excel 2010 or later, using AGGREGATE:
=AGGREGATE(15,6,A1:A10/(A1:A10<>0),1)
EXCELXOR 