Return Entry Corresponding to Maximum Value Based on Conditions 39

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:

INDEX_MAX_IF Non-array Alternative

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”.

More…

Advanced Formula Challenge #6: Results and Discussion Reply

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!).

More…

IFERROR techniques for excluding certain values from results 6

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)

More…