• 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.

UDF for Geomeanif

Kabeli

New Member
Hello community
Can you please help me figure out how to write a user defined function for GeomeanIF that is similar to AVERAGEIF function, I want to write a Macro similar the module below but for GEOMEAN:
>>> use code - tags <<<
Code:
Sub Average1()
' To calculate arithmetic average of the relatives and put it row by row
'
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Sheets("Sheet1") ' Change to your sheet

With ws
    lastrow = .Range("C" & .Rows.Count).End(xlUp).Row
    With .Range("A1:I" & lastrow)
        .Offset(, 9).Value = .Value
    End With
    With .Range("G2:I" & lastrow)
        .Offset(, 9).FormulaR1C1 = "=AVERAGEIF(C1,RC1,C[-9])"
        .Offset(, 9).Value = .Offset(, 9).Value
    End With
    '.Range("F1:I" & lastrow).RemoveDuplicates 1, xlYes
     'Range("F:G").Copy Worksheets("Sheet2").Range("F:G")
    ' Worksheets("Sheet2").Range("A:D") = Range("F:I").Value
End With

End Sub
 
Last edited by a moderator:
Maybe you don't need a UDF, perhaps a formula of this ilk: =GEOMEAN(IF($A$2:$A$11=$A2,G$2:G$11))
 
Maybe you don't need a UDF, perhaps a formula of this ilk: =GEOMEAN(IF($A$2:$A$11=$A2,G$2:G$11))
I need a UDF because I am dealing with multiple id's for about 48900 rows (this number deviate from time to time, in some instatnce by 200 to 800), therefore I need the geomean for each one of them.
 
I need a UDF because I am dealing with multiple id's for about 48900 rows (this number deviate from time to time, in some instatnce by 200 to 800), therefore I need the geomean for each one of them.
That's what the formula will do. A UDF won't do any better.
Why don't you attach a workbook so that we get a handle on the real problem?
 
Back
Top