5 Replies Latest reply on Dec 14, 2011 2:00 AM by jeremywebber

    Ranking using INDEX() - but only showing values either side of selected value

      Hi,

       

      Rather than showing a ranking for the top 20 countries by a selected dimension, I'm trying to show the countries ranked three either side of a Country (which is selected using a parameter).

       

      So for example if I selected France and it was ranked 6th in terms of the chosen dimension (sales, or exports, or profits etc .....), I'd like to produce a table showing the USA (3rd), UK (4th), Italy (5th), France (6th), Germany (7th), Belgium (8th) and the Netherlands (9th).

       

      If France was ranked 1st, then it should show the top 4 countries and if it were last, it should show the bottom 4 countries.

       

      I can't work out what sort of calculation I need in order to compare the index number of the selected country with those above or below it?

       

      Thanks in advance