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.

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