Formula to UDF - String Data

[Juggler_IN]

Hi,

I have a formula to Rank data, say in Range("A1:A5"). The formula is: <a1) countif($a$1:$a$5,$a$1:$a$5&""))+1<a1)="" countif($a$1:$a$5,$a$1:$a$5&""))+1[="" code]
<a1) countif($a$1:$a$5,$a$1:$a$5&""))+1[="" code]
<a1) countif($a$1:$a$5,$a$1:$a$5&""))+1
SUMPRODUCT(( $A$1:$A$5 < A1 ) / COUNTIF( $A$1:$A$5 , $A$1:$A$5&"" )) + 1
<a1) countif($a$1:$a$5,$a$1:$a$5&""))+1
Output, for a sample data in A1 to A5, is:

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Apple[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

I tried to convert it into a UDF but I am not getting an executable UDF. The UDF is:

Public Function RS(ByVal rngItem As Range, ByRef rngData As Range) As Double
    
    With Application.WorksheetFunction
            RS = .SumProduct((rngData < rngItem) / .CountIf(rngData, rngData & "")) + 1
    End With


End Function

What am I missing here?</a1)></a1)></a1)></a1)>

 

[Juggler_IN]

@Rick Rothstein; @RoryA; @Marcelo Branco;

Adding Application.Volatile does help to get the correct output (by pressing F9 to re-calculate) BUT it gives the results of the previous dataset, and not the current one, IF THE DATA IS NUMERIC.

Public Function RS(ByVal rngItem As Range, ByRef rngData As Range) As Double

    Dim strFor As String


        strFor = Replace("=SumProduct((@" & "<" & rngItem.Address & ")/CountIf(@,@&""""))+1", "@", rngData.Address)
        RS = Evaluate(strFor)
        Application.Volatile
        Calculate


End Function

 

[Juggler_IN]

@Rick Rothstein; @RoryA; @Marcelo Branco;

Any insights on the "lagged" behavior of the UDF? is it populating the output of the previous dataset once the dataset gets updated. (For eg. if you use RANDBETWEEN for random data to be sorted.)

 

[RoryA]

If you're using a volatile function like that, I'd guess it's recalculating after the UDF.

 

[shg]

If you're using a volatile function like that, I'd guess it's recalculating after the UDF.

Shouldn't.

Juggler, did you read Rory's question in post #10 ?

 

[Marcelo Branco]

I *think* I'm understanding your problem.

I suppose:
There is a fruit List, say, in D2: D13
There are formulas in A2:A6 like this (or similar)
=INDEX(D$2:D$13,RANDBETWEEN(1,12))


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1
​

[/TD]
[TD]

Random​

[/TD]
[TD]

Rank​

[/TD]
[TD][/TD]
[TD]

List​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2
​

[/TD]
[TD]

Papaya​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

Apple​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3
​

[/TD]
[TD]

Orange​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

Avocado​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Avocado​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Banana​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Avocado​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Blackberry​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Melon​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

Chery​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Grape​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Lemon​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Mango​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Melon​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Orange​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Papaya​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Strawberry​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


UDF (see post 4)

Public Function RS(rngItem As Range, rngData As Range) As Double
    Dim strFor As String
    
    strFor = Replace("=SumProduct((@" & "<" & rngItem.Address & ")/CountIf(@,@&""""))+1", "@", rngData.Address)
    RS = Evaluate(strFor)
End Function

Formula in B2 copied down
=rs(A2,A$2:A$6)

Then pressing F9 the values in B2:B6 don't change; but selecting any cell in A2:A6 and pressing F2 and Enter it worked for me.

I don't know why it doesn't work with F9

M.