3 Replies Latest reply on Mar 14, 2012 12:08 PM by Nathan Schofield

    Group if not in Top X

    Nathan Schofield

      I have a set of data that I want to group a certain way.


      To simplify this I have illustrated with a dummy example in the attached packaged workbook.


      In this example I have 'Country' and 'Value'.


      I want to create a grouping of 'Country'. If the country is in the Top 5 by value I want the country name to be displayed. Otherwise I want the countries to be grouped as 'rest'.


      I am aware that this grouping can be done manually, but I want this as a calculation, because I will be updating the data frequently, and there are alot of 'countries' not in the top 5.


      I have tried a table calculation using 'INDEX()' to group but this doesn't work (see attached screenshot).


      Help very much appreciated.