Divide Numbers into Deciles

[Bobaree]

Column D contains decimal numbers which are the summation of 6 other columns. (The 6 other columns are percent rank calculations.) Does anyone know how to divide the numbers in column D into deciles and show the results in column C?

I found the information below which looks like it would do the trick, but I don't know how to incorporate it into my project. In addition to not knowing how to incorporate the code, I have reservations about using it since the results in column D are already percentranks. In other words, I'm not sure about applying the percentrank formula against those numbers that are already percentranks.

Can anyone help? I will be glad to post my current spreadsheet if that will help.


This is the information I found:
I have written a user defined function that will look down a range and calculate which decilerank for a number within the range sits.<o></o>
Copy the code to a module in your spreadsheet and call it as you normally would a function.<o></o>
' This User Defined Function can be called to calculate which Decile a single cell
' falls within a larger range of cells<o></o>
Function PERCENTILE_TO_DECILE(DataRange, RefCell)<o></o>
'DECILE_RANK(The Range of data you are interested in, The data cell that you want to know the decile of)
'Declares the function that can be called in the spreadsheet cell - enter '=DECILE_RANK(A5:A50,A5)'
'to use regularly paste it to your PERSONAL.xls workbook and reference it via =PERSONAL.XLS!decile_rank(A5:A50,A5)<o></o>

'Remove quote on line below if you want to Automatically update the function when the worksheet is recalculated
'Application.Volatile True<o></o>
'Using the percentile worksheet function calculate where the 10th, 20th etc percentile of the reference range are<o></o>
DEC1 = Application.WorksheetFunction.Percentile(DataRange, 0.1)
DEC2 = Application.WorksheetFunction.Percentile(DataRange, 0.2)
DEC3 = Application.WorksheetFunction.Percentile(DataRange, 0.3)
DEC4 = Application.WorksheetFunction.Percentile(DataRange, 0.4)
DEC5 = Application.WorksheetFunction.Percentile(DataRange, 0.5)
DEC6 = Application.WorksheetFunction.Percentile(DataRange, 0.6)
DEC7 = Application.WorksheetFunction.Percentile(DataRange, 0.7)
DEC8 = Application.WorksheetFunction.Percentile(DataRange, 0.8)
DEC9 = Application.WorksheetFunction.Percentile(DataRange, 0.9)<o></o>

' Calculate the Decile rank that the reference cell value sits within<o></o>
If (RefCell <= DEC1) Then DECILE_RANK = 1
If (RefCell > DEC1) And (RefCell <= DEC2) Then DECILE_RANK = 2
If (RefCell > DEC2) And (RefCell <= DEC3) Then DECILE_RANK = 3
If (RefCell > DEC3) And (RefCell <= DEC4) Then DECILE_RANK = 4
If (RefCell > DEC4) And (RefCell <= DEC5) Then DECILE_RANK = 5
If (RefCell > DEC5) And (RefCell <= DEC6) Then DECILE_RANK = 6
If (RefCell > DEC6) And (RefCell <= DEC7) Then DECILE_RANK = 7
If (RefCell > DEC7) And (RefCell <= DEC8) Then DECILE_RANK = 8
If (RefCell > DEC8) And (RefCell <= DEC9) Then DECILE_RANK = 9
If (RefCell > DEC9) Then DECILE_RANK = 10<o></o>

'If you want to check that there is an empty cell value in the 'Ref Cell" reference cell then remove the quotes from the 6 lines below<o></o>
'ErrorSum = 0
'If Len(RefCell) = 0 Then ErrorSum = ErrorSum + 1
'For Each Cell In DataRange
'If Len(Cell) = 0 Then ErrorSum = ErrorSum + 1
'Next
'If ErrorSum > 0 Then MsgBox ("There is an empty cell in the lookup ranges for the decile function")<o></o>
End Function<o></o>

 

[Bobaree]

Beautiful! It works perfectly when I copy it into the spreadsheet. Is there a way to incorporate the formula into VBA code rather than entering into spreadsheet?

 

[Andrew Poulsom]

What do you mean by "incorporate the formula into <ACRONYM title="visual basic for applications">VBA</ACRONYM> code"? Where do you want the results saved?

 

[Bobaree]

I want the results saved in the column just like you did it. I just thought maybe there was a way to make this a "Sub Routine ()", but I am so new to VBA that I have a very limited understanding of what is possible and the best ways to achieve desired goals.

When I copied the formula from the uppermost cell and tried to paste it to the last row of my spreadsheet (by hitting Ctrl+Shift+Down Arrow), it did not stop at the last row of my spreadsheet, but instead copied to the very bottom of the entire Excel spreadsheet.

Thank you for bearing with this beginner.

 

[Andrew Poulsom]

To copy the formula down point at the square at the bottom right of the cell until a + appears and double-click.

 

[Bobaree]

That works, and I'm sure I can record a macro to make it repeatable. Thank you so very much for sharing your expertise with me. Bobaree