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?

    ReplyDelete