• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Formula to UDF

D

dishantrai

New Member
Hello,

Can anyone please help me with the code for the below formula so that I dont need to manually type this formula every time on different

=IF(COUNT(C8:C57<6,"",IF(MOD((35/100)*COUNT(C8:C57),1)>0,SMALL(C8:C57,INT((35/100)*COUNT(C8:C57))+1),AVERAGE(SMALL(C8:C57,INT((35/100)*COUNT(C8:C57))),SMALL(C8:C57,INT((35/100)*COUNT(C8:C57))+1))))
 
Lazy way:
Code: 
Function blah(rng)
Z = rng.Address(external:=True)
zz = Evaluate("SMALL(" & Z & ",INT((35/100)*COUNT(" & Z & "))+1)")
zz2 = Evaluate("SMALL(" & Z & ",INT((35/100)*COUNT(" & Z & ")))")
If Evaluate("COUNT(" & Z & ")<6") Then
  blah = ""
Else
  If Evaluate("MOD((35/100)*COUNT(" & Z & "),1)>0") Then blah = zz Else blah = Application.WorksheetFunction.Average(zz, zz2)
End If
End Function
used: =blah(C8:C57)

I have a feeling (because I haven't looked too closely) that this might be the same/similar to the built-in function
=PERCENTILE.INC(C8:C57,0.35)

I don't see why
MOD((35/100)*COUNT(C8:C57),1)>0
is included; it's only ever false if .35*the count of numbers is a round number, in this case only when there are exactly 20 or 40 numbers.
 
p45cal said:
Lazy way:
Code: 
Function blah(rng)
Z = rng.Address(external:=True)
zz = Evaluate("SMALL(" & Z & ",INT((35/100)*COUNT(" & Z & "))+1)")
zz2 = Evaluate("SMALL(" & Z & ",INT((35/100)*COUNT(" & Z & ")))")
If Evaluate("COUNT(" & Z & ")<6") Then
  blah = ""
Else
  If Evaluate("MOD((35/100)*COUNT(" & Z & "),1)>0") Then blah = zz Else blah = Application.WorksheetFunction.Average(zz, zz2)
End If
End Function
used: =blah(C8:C57)

I have a feeling (because I haven't looked too closely) that this might be the same/similar to the built-in function
=PERCENTILE.INC(C8:C57,0.35)

I don't see why
MOD((35/100)*COUNT(C8:C57),1)>0
is included; it's only ever false if .35*the count of numbers is a round number, in this case only when there are exactly 20 or 40 numbers.
Click to expand...
Thank you. I will try this one and let u know if it is working. Yes it is a percentile used in the tax law in my country
 
I also made something by googling stuff. You guys can evaluate if there is any error in my code

Code: 
Option Explicit
Function PercentileInd(MyRange As Range, Percentile As Integer)

Dim rng As Long

rng = WorksheetFunction.Count(MyRange)

If rng < 6 Then

PercentileInd = ""

Else

If Percentile * rng / 100 = Int(Percentile * rng / 100) Then
PercentileInd = (WorksheetFunction.Small(MyRange, Percentile * rng / 100) + WorksheetFunction.Small(MyRange, (Percentile * rng / 100) + 1)) / 2
Else
PercentileInd = WorksheetFunction.Small(MyRange, WorksheetFunction.RoundUp(Percentile * rng / 100, 0))


End If

End If

End Function
 
Last edited by a moderator:
dishantrai said:
Is there a way we can modify the code to run on filtered / visible cells?
Click to expand...
I haven't tested this thoroughly:
Code: 
Function blah3(rng, pcntle)
Z = rng.Address(external:=True)
cnt2 = Evaluate("AGGREGATE(2,5," & Z & ")")    'count
myMod = Evaluate("MOD((" & 35 & "/100)*" & cnt2 & ",1)")
k = Int(cnt2 * pcntle / 100)
zzb = Evaluate("AGGREGATE(15,5," & Z & "," & k + 1 & ")")    'small
zzc = Evaluate("AGGREGATE(15,5," & Z & "," & k & ")")    'small
If cnt2 < 6 Then
  blah3 = ""
Else
  If myMod > 0 Then
    blah3 = zzb
  Else
    blah3 = Application.Average(zzb, zzc)
  End If
End If
End Function
 
You must log in or register to reply here.
Back
Top