title: Creating Bins/groups in Excel
body: <p><strong>Background:</strong></p>
  
  <p>I want to create bins/groups in Excel based on a column value,for example: if column "A" contains  values from second row till 501 row and each row contains different values(in float point decimal) in the same column (column A), then i want to define a range of values in column "B" as group1 to group5 (we can mark them as 1 ,2,3,4 and 5) , so that each group have 100 row values for column "B" as 1,2,3,4 or 5.</p>
  
  <p><strong>My attempt:</strong></p>
  
  <p>What i am trying is two way, first i sort the data into ascending order of column "A" then use the below formula to tabulate groups/bins.It works perfectly, but hangs when there is a big chunk of data.</p>
  
  <pre><code> =INT((ROWS($A$1:A1) - 1) * 5 / ROWS($A$1:$A$501))+1
  </code></pre>
  
  <p><strong>My question:</strong></p>
  
  <p>What i require is to calculate these bin values efficiently may be using formula like percentile, i also tried it but its giving me <em>one</em> value for a range (which is obvious).
  The row count formula (present above) also doesn't quite seem to work if there are ties in the values present in "A", i.e.the same value can be on two different groups if present at the boundary of bins(groups).</p>
  
  <p>I hope my problem is clear, thanks in advance and any help is much appreciated.</p>