Ranking portions of data

**blueorchid** · 03-18-2013, 12:55 PM

I have a problem with ranking of data.

I have 4,300 cells populated with data. This data has been sorted and now I need to rank the data in 1% increments. For example, the first 1% of data (43 cells) will be ranked #1, the second 1% of data will be ranked #2 etc. How can I automate this process? I tried playing around with the "Rank" command but that did not seem to work.

Thanks so much

**FDibbins** · 03-18-2013, 01:00 PM

Hi and welcome to the forum

It would probably help if we could see a small potion of the data you are working with (without any sensitive data)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Excel&macrosBeginner** · 03-18-2013, 02:23 PM

Hi,

use this =RANK(A1,$A$1:$A$43)+COUNTIF($A$1:A1,A1)-1

**blueorchid** · 03-18-2013, 05:46 PM

Hello. Thank you for your help. I tried your formula and it didnt seem to work. I maybe doing something wrong though. Martindwilson on another subforum posted the following answer. I tried it and it works. Thank you again for replying.

if you just want to put 1 against the 1- 43 then 2 from 44-86 and so on
=CEILING(ROWS($A$1:A1)/43,1)