On 2002-03-09 14:18, John McGraw wrote:

I tried the variant approach, and it still doesnt work. I simplified the code greatly to JUST focus on getting a function to work with an array formula:

----

Function MyAverage(DRange as Range)As Variant

MyAverage = DRange.Range("A1").Value

End Function

----

In this attempt, I am only trying to assign the function with the value of the 1st cell in the range. It works if I dont enter the function in an array formula, but as an array formula, I still get "#VALUE!"

I'm totally stumped. (I am pressing CTRL-SHIFT-ENTER)

This worked for me (With and Without CSE)

Function MyAverage(DRange As Range) As Variant

MyAverage = DRange.Range("A1")

End Function

But, i feel you need this:

Function AverageIf(Range As Range, Criteria As String, Optional Average_Range As Range) As Variant

Dim T As String

If Not Evaluate("OR(" & Left(Criteria, 1) & "={""<"","">"",""=""})") Then _

Criteria = "=" & Criteria

If Average_Range Is Nothing Then

AverageIf = Evaluate("AVERAGE(IF(" & Range.Address & Criteria & "," & Range.Address & "))")

Else

AverageIf = Evaluate("AVERAGE(IF(" & Range.Address & Criteria & "," & Average_Range.Address & "))")

End If

End Function

Which works EXACTLY like SUMIF, no need to array enter.

## Like this thread? Share it with others