Multiple arrays in Median function -- VBA

S

Scott P

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?
 
S

Scott P

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

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

Scott said:
I am trying to implement a function that I built using Excel in VBA.
Click to expand...
My function is: {=MEDIAN(IF((A1:A10>0)*(A1:A10<5),B1:B10)} This
Click to expand...
function tells me: calculate the median for the values in the range
Click to expand...
 
F

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
 
Top