Yesterday we showed how to use the maximum function creatively and not only find the maximum value, but also the location of the maximum value.
Now let's dress it up further and also capture the contents that correspond to the maximum value, in this case the rep number.
For good measure, we also threw in a section for the minimum performing rep. The formula is the similar to the maximum, only we switch MIN for MAX.
The file is called Index. Both the exercise and result are in the same file.
Here is what we started with:
To get the rep number, we will use the INDEX function, which has three parts:
Where are we looking- the array or range
The row number
The column number
Here is the result:
Our formula is =INDEX(rep,E4)
Let's review our parts:
Where?- we have specified a named range for the rep column called "rep", so we use that
Row number- we link to the row number that comes from the maximum formula
Column number- not applicable, since we are looking down one column
If we are looking for a value across a long row, then we would leave the second argument blank (by typing in ",,") and then specifying a column.
If we were looking for a specific point in a rectangular range, then we would specify both the row number and the column number.
INDEX is a nice function for returning values from a specified point in the range. We will use this again in future lessons.