How to get more than just the ordinary information on the largest value. But first, a refresher on the MAX function.
The exercise is the Maximum Exercise file. The result is the Maximum Result file.
We have a list of number of orders by rep:
The formula to calculate the Max value is: =MAX(orders). Note that we used a good programming practice, assigning a named range, orders, to the order column.
Note also in Excel 2002, MAX is one of the options you can select via the down arrow in the Sum icon button.
Suppose you have a long list. Where is the MAX value anyway? Sure you could fish for it, but why bother?
Once again, we bring an array to the rescue to show where the maximum value. Here is the formula:
{=MIN(IF(orders=MAX(orders),ROW(orders), ""))
Let's break the formula down, from inside out:
orders=MAX(orders)- we look for the maximum value of orders
ROW(orders)- is the row number
IF(orders=MAX(orders),ROW(orders), "")- we put the two together in an IF statement, returning a blank value for any row that is not the max row
=MIN(IF(orders=MAX(orders),ROW(orders), "")- we find the minimum value. There will only be one value, since we only returned a value for the maximum.
{=MIN(IF(orders=MAX(orders),ROW(orders), ""))- we close the formula as an array (signified by the brackets) by clicking Ctrl, Shift and Enter together at the same time.
OK, this formula is more advanced. Not as easy to swallow. Regardless, you know where to find it on our site for your future reference.