Multiple arrays in Median function -- VBA
 

I am trying to implement a function that I built using Excel in VBA. My function is: {=MEDIAN(IF((A1:A100)*(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?

Multiple arrays in Median function -- VBA
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Scott P wrote:
I am trying to implement a function that I built using Excel in VBA.
My function is: {=MEDIAN(IF((A1:A100)*(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?

Multiple arrays in Median function -- VBA
 

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.

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany


Scott P wrote:
I am trying to implement a function that I built using Excel in VBA.
My function is: {=MEDIAN(IF((A1:A100)*(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?

Multiple arrays in Median function -- VBA
 

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:A100)*(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 P wrote:

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

My function is: {=MEDIAN(IF((A1:A100)*(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?

Multiple arrays in Median function -- VBA
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Scott P wrote:
I am trying to implement a function that I built using Excel in VBA.
My function is: {=MEDIAN(IF((A1:A100)*(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?