Multiple arrays in Median function -- VBA

[Guest]

I am trying to implement a function that I built using Excel in VBA. My function is: {=MEDIAN(IF((A1:A10>0)*(A1:A10<5),B1:B10)} This function tells me: calculate the median for the values in the range B1:B10 for which the corresponding values in A1:A10 are greater than 0 and less than 5.

How can I enable the same functionality in VBA?

 

[Frank Kabel]

Hi Scott
do you want to insert this formula in a cell via VBA?

 

[Guest]

Hi Frank,

No -- I would like the result of this calculation to appear in a cell in an Excel worksheet. I would like the calculation itself to take place within VBA. Thanks in advance for your help.

 

[Jerry W. Lewis]

What do you want to pass to the function vs. having hardcoded? Do you
want to code the functionality in VBA, or is it sufficient to have Excel
process it as an array function from VBA?

Application.Evaluate() will process the argument as though it was array
entered. Thus two possibilities would be

Function myMedian()
myMedian = Application.Evaluate( _
"MEDIAN(IF((A1:A10>0)*(A1:A10<5),B1:B10))")
End Function

called as =myMedian()

Function myMedian(data As Range, condition As Range)
myMedian = Application.Evaluate("MEDIAN(IF((" & _
condition.Address(external:=True) & ">0)*(" & _
condition.Address(external:=True) & "<5)," & _
data.Address(external:=True) & "))")
End Function

called as =myMedian(B1:B10,A1:A10)

Jerry

I am trying to implement a function that I built using Excel in VBA.

My function is: {=MEDIAN(IF((A1:A10>0)*(A1:A10<5),B1:B10)} This

function tells me: calculate the median for the values in the range

 

[Frank Kabel]

Hi Scott
to be honest I would try something like the following:
application.screenupdating=false
with activesheet.range("C1")
.formulaarray="=MEDIAN(IF((A1:A10>0)*(A1:A10<5),B1:B10)"
.value=.value
end with
application.screenupdating=true