If you're using Excel 2002 or higher, select your formula cell and goto the
menu ToolsFormula AuditingEvaluate Formula. Repeatedly click the Evaluate
button and you'll see *exactly* how Excel is calculating the result.

--
Biff
Microsoft Excel MVP


"hdf" wrote in message
...
On Feb 15, 10:07 pm, Ron Rosenfeld wrote:
On Fri, 15 Feb 2008 12:27:13 -0800 (PST), hdf wrote:
Ron,

Thank you, I will try your suggestion. However, what I don't
understand is that my range TABLE has no 0's in it, so they should not
be counted, otherwise everytime you use the SMALL function it would
give you a value of 0. The smallest number in my TABLE is 1.

Am I missing something here?

Yes you are. You are not understanding what your formula is doing.

Your SMALL function is evaluating the results of the array generated by
this
formula:

(TARGET=1)*TABLE

TARGET=1 evaluates to {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

TABLE evaluates to {1;2;3;4;5;6}

When you multiply one by the other, you get {1;2;3;0;0;0}

The two smallest numbers in that array are both 0.

--ron

I thought by doing an array with SUM as in this case, the * was
interpreted as an AND function. It should not be multiplying them -
it should be summing the value of the events that meet the criteria -
as it does in my LARGE example where the answer is 5 (2 + 3). If it
was multiplying it would be an answer of 6.

When I use the same formula with with more than one range in
Parantheses and separated by a * (e.g. a second condition), it works
as expected with LARGE (summing the two largest values of the
resultant data set that has been filtered by my conditions- it does
not multiply them).

Something is not working, but I don't know why, since it works just
fine with LARGE.

Thanks,

Hector