Dividing list into quintiles, returning a value but ignoring blanks.

Could you post some dummy sample data?

Here is an example. B1:B17 is what I need a function for. (In my case the range is J1:J6578)
In this exaple the range/# of ranks is divisible by 5, but this might not be the case for later ranges as the selection varies.

------A(Rank) B(Quintile)
1-----4--------------2
2-----Blank
3-----Blank
4-----7--------------4
5-----1--------------1
6-----9--------------5
7-----Blank
8-----3--------------2
9-----Blank
10----8--------------4
11----2--------------1
12----Blank
13----6--------------3
14----Blank
15----10-------------5
16----Blank
17----5--------------3

Here's one way of doing it. It's a little scruffy, but it's effective

Change the range length in the small lookup table (the yellow cells) to fit your real data.
The quintile values will adjust themselves depending on the values in your data and it then uses VLOOKUP to put the right quintile number next to the values.

An alternative is
In F1:

Formula:

Please Login or Register  to view this content.

then insert this formula:
=IF(J2="","",ROUNDUP(J2/$F$1*5,0))