LARGE() is a simple formula for finding the largest value in a data range. It works equally well in a two dimensional array as it does for a single column list. For a single column of data its then easy to use and INDEX() and MATCH() combination to find what it relates to.
For example data in two columns – the first showing a sales rep name and the second column showing the values of the sales they have made. The LARGE() formula produces the largest value in the second column and the INDEX(MATCH) combination uses the result of the LARGE() formula to produce the name of the sales rep that sold the largest value. You can do the same for the second largest sales, third largest sales etc.
Easy enough? Yes and no!
The technique above works where every sales value is different but only picks the first one found if there are identical sales values.
If there are potentially identical values in the single column of data then my solution is:
- to create a second version of the data which adds a unique very small value to each item in the data list.
- I use the ROW()/1000 formula to derive the very small number achieve this: suppose in cell c15 I have the value 1,000 then to create the unique number I use the formula C15+ROW(C15)/1000 which give the answer 1,000.015.
- If another value of 1,000 appears on row 67 then the result would be 1,000.067
- Thus we can be sure that we have all unique values in the list which can the be successfully used by the LARGE() and INDEX(MATCH) technique explained above
The technique can be extended to a two dimensional range but in this case we can’t use MATCH() as it only works on a single column or row: Suppose we have a list of sales reps with the monthly sales they have achieved in a two dimensional table then the steps are:
- Create a second version of the table by using =c5+row(C5)/1000+Column(C5)/1000000.
- This will give a result like 962.045011 where 962 is the original data, 0.0 give the row number i.e. 45 and .xxx011 gives the column number i.e. 11
- NB these row and column numbers refer to rows and columns in the whole sheet rather than just the data table i.e. column 11 = col K, column 52 would be column AZ
- Use the large formula to find the largest value in this second table
- use =INT((C3-INT(C3))*1000) to extract the row number
- use =ROUND(((C3-INT(C3))*1000-ROUND((C3-INT(C3))*1000,0))*1000,0) to extract the column number
- The row number can be used in an INDEX() formula to obtain the sales rep name and the column number can be used in an INDEX() formula to identify the month
Excel Monkey 