Sometimes you need to find not only the largest or maximum number for all of your data, but also the largest number in a subset, for example the largest positive or negative number.
How to find the largest negative value less than 0 in the given Excel formula
If the amount of data is small, the task can be easily accomplished by manually selecting the appropriate range for the MAX function.
In other cases, such as a large unsorted data sample, it may be difficult or even impossible to select the correct range.
By combining the IF function with the MAX function in an array formula, it is easy to set conditions (such as only positive or negative numbers) so that only data that meets those parameters is tested by the formula.
