Using Excel statistical functions on a subset of an array

[avi]

Hello,

From a 2 columns Excel range, I have produced a 2 dimensional array
with 2 columns

Is is possible to use the powerful Excel statistical functions (as
Stdev) on the second array column values (actually a subset of
values), while a ceratin condition on the first column holds

I am aware of the SUMPRODUCTS speacial function but it does'nt seem to
apply to statistical functions

Thanks a lot
Avi

 

[Jerry W. Lewis]

If you are asking about worksheet formulas, you could use an array formula like
=STDEV(IF(col1_condition,col2))

Note that this will not work with LINEST and related functions, which do not
permit non-numeric data within the input range.

If col2 has some empty cells where col1_condition is satisfied, and you do
not want those empty cells to be considered to be zero values, then you would
need a compound condition like
=STDEV(IF(col1_condition*ISNUMBER(col2),col2))

Since this is the programming group, you may be asking about VBA, where it
becomes more complicated since VBA does not directly support array formulas.
However, if the corresponding worksheet formula can be written as a character
string (say stored in a VBA variable called formula) where the formula uses
explicit cell references rather than VBA arrays, then the VBA expression
Evaluate(formula)
will evaluate as though formula had been array entered into a worksheet cell.

Jerry

 

[avi]

Thanks a lot

It looks very promising

Avi