SMALL function seems not to work correctly
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
|