### Dashboard Top 5 Values / Bottom 5 Values

There are times when we want to generate top and low values on dashboards.

It's easy when you know the Large and Small function.

In this example I have a set of data in Column A. I use the =Large formula in column B and =Small formula in column D to represent the sub dataset I need.

 Data Top 5 Values Top Formula Lowest 5 Values Lowest Formula 91 =LARGE(A:A,1) 28 =SMALL(A:A,1) 87 87 =LARGE(A:A,2) 29 =SMALL(A:A,2) 79 84 =LARGE(A:A,3) 34 =SMALL(A:A,3) 51 80 =LARGE(A:A,4) 35 =SMALL(A:A,4) 69 79 =LARGE(A:A,5) 45 =SMALL(A:A,5) 29 28 66 34 91 80 56 55 71 74 84 79 45 56 63

Syntax
LARGE(array,k)
Array     is the array or range of data for which you want to determine the k-th largest value.
K     is the position (from the largest) in the array or cell range of data to return.
If array is empty, LARGE returns the #NUM! error value.
If k ≤ 0 or if k is greater than the number of data points, LARGE returns the #NUM! error value.
If n is the number of data points in a range, then LARGE(array,1) returns the largest value, and LARGE(array,n) returns the smallest value.

You can use =ISError to remove the error, =ISError(large(A:A,1),””)

So the next time you are asked to generate top values, break out this trick and WOW your audience.

#### 1 comment:

1. How can I use this defined by specific array? IE if I have depot numbers along the 1st column and my query is defined by depot number then large / small function?