I'm typing the following into the detination cell
"=decile_rank(E2:E60,I2)"
On Jan 9, 1:06 pm, "dguillett1" <dguille...@gmail.com> wrote:
> Without looking too close or testing:
> 1. is it in a REGULAR module. Will NOT work in sheet module
> 2. DataRange is asking for a range such as a1:b10
> 3. RefCell is asking for a cell such as b12
> Hope this helps
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
>
>
> -----Original Message-----
> From: Eddie
> Sent: Monday, January 09, 2012 6:54 AM
> To: MS EXCEL AND VBA MACROS
> Subject: $$Excel-Macros$$ UDF
>
> 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- Hide
> quoted text -
>
> - Show quoted text -
--
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
