How do I get this UDF to function correctly? I keep getting #name? in
the destination cell.
Thanks in advance.
Ed.
' This User Defined Function can be called to calculate which Decile a
single cell
' falls within a larger range of cells
Public Function DECILE_RANK(DataRange, RefCell)
'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)
'Remove quote on line below if you want to Automatically update the
function when the worksheet is recalculated
'Application.Volatile True
'Using the percentile worksheet function calculate where the 10th,
20th etc percentile of the reference range are
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)
' Calculate the Decile rank that the reference cell value sits within
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
'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
'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")
End Function
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
