Conditional worksheet function function that returns range

A

alex

Greetings!

Excel's (2000) SUMIF and COUNTIF functions are useful, but I often
find myself longing for more general functionality that allows me to
apply all of Excel's math functions on ranges matching certian
criteria. Does anybody know of a worksheet function that returns a
range object matching certian criteria? ie:

magic_range_function(criteria_range,criteria,returned_range)

I'd like to use such a function to apply any of Excel's math functions
to contitional ranges, such as:

max(magic_range_function(...))

Is this possible in Excel?

Thanks!

- Alex (aferguso~at~nrcan~dot~gc~dot~ca)
 
P

Peo Sjoblom

There are workarounds for this using array formulas, there is not a single
magic function but depending on criteria there are ways of doing it.
Using your example of max you could use

=MAX(IF(A1:A10<5,A1:A10))

where you would find the max value less than 5 in A1:A10
it has to be entered with ctrl + shift & enter
What is basically does is that it will return an array of the numbers in
A1:A10
that are less than 5 The array might look like

{2;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

then when max is applied to that it will return 4

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
B

Bernard Liengme

Are you familiar with array functions?
Let's say I want to find the MAX value of the values in A1:A100 that are
less that 10.
Enter =MAX(IF(A1:A100<10,A1:A100)) but complete the formula with
Shift+Ctrl+Enter rather than just Enter
Excel will enclose the formula in braces {...}

Best wishes
 
Top