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
35	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.