"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, the multiplication form does work but the ISNUMBER(MATCH(...))
method is better. I haven't done a calc time test, though.

In the (...)*(...) form you're evaluating each element (cell_ref) of the
array against *all* 4 variables. This returns 4 intermediate results for
each element.

The ISNUMBER(MATCH(...)) method evaluates each element of the array
against *any* of the variables and returns only a single intermediate
result for each element. So, logic tells me that this should be more
efficient.

Peo's comments (which make for a compelling argument for "knowing" your
data before constructing formulas to analyze it) notwithstanding, I would
be interested in seeing such a time test. Although I understand your
logic, my experience with other languages tells me that straight
comparisons are almost always much faster than calls into a function plus
that function's code execution (and here, you are executing two separate
function calls); plus, one would think that underneath the MATCH function
call is a series of comparison operations not too dissimilar to those
being done in the modification I proposed to your formula (although true,
the MATCH function's code would be at compiled code speed whereas the
formula comparisons would be at interpreted code speed). I'm thinking that
in the end, more than likely, you are probably right, but seeing a time
test would be interesting.

Rick

Calc times screencap:

http://img110.imageshack.us/img110/5...lctimeshm8.jpg

As you can see the the larger the range the more "significant" the
difference.

Calc timer code (thanks to Charles Williams):

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

--
Biff
Microsoft Excel MVP