SMALL function seems not to work correctly

[hdf]

I can't seem to get the SMALL function to work for me the same way the
LARGE function does.

Say I have a table of two columns with the data in the first column
A1:A6 (range called TABLE) being secuntial numbers 1 thru 6 and a
second column B1:B6 (range called TARGET) with B1:B3 each = 1 and
cells B4:B6 each = 0.

When I put the following formula in C1 as an array formula

{=SUM(LARGE((TARGET=1)*TABLE,ROW(INDIRECT("1:2"))))}

It gives me the right answer, or at least what I expected it to give
me, the value of 5. It sums the two largest numbers in the range
TABLE that first met the criteria set out for the range TARGET.

However, when I use the exact same formula, but substitute the LARGE
function for SMALL, it gives me a value of 0. I thought it should
give me a value of 3 (which is the sum of the two smallest numbers
that meet my criteria).

{=SUM(SMALL((TARGET=1)*TABLE,ROW(INDIRECT("1:2"))))}

What is wrong?

 

[Ron Rosenfeld]

I can't seem to get the SMALL function to work for me the same way the
LARGE function does.

Say I have a table of two columns with the data in the first column
A1:A6 (range called TABLE) being secuntial numbers 1 thru 6 and a
second column B1:B6 (range called TARGET) with B1:B3 each = 1 and
cells B4:B6 each = 0.

When I put the following formula in C1 as an array formula

{=SUM(LARGE((TARGET=1)*TABLE,ROW(INDIRECT("1:2"))))}

It gives me the right answer, or at least what I expected it to give
me, the value of 5. It sums the two largest numbers in the range
TABLE that first met the criteria set out for the range TARGET.

However, when I use the exact same formula, but substitute the LARGE
function for SMALL, it gives me a value of 0. I thought it should
give me a value of 3 (which is the sum of the two smallest numbers
that meet my criteria).

{=SUM(SMALL((TARGET=1)*TABLE,ROW(INDIRECT("1:2"))))}

What is wrong?

So far as Excel is concerned, 0 is also a number. So here's one way to exclude
the 0's:

=SUM(SMALL(IF(TARGET=1,TABLE),{1,2}))

(array-entered)
--ron

 

[hdf]

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?

Hector

 

[Ron Rosenfeld]

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

 

[hdf]

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

 

[T. Valko]

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

 

[Sandy Mann]

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

fine with LARGE.

Ron has already given you an explanation. One way around it is to use an
IF() statement with a very large numbers as its FALSE element:

=SUM(SMALL(IF(B1:B6=1,A1:A6,1E+307),ROW(INDIRECT("1:2"))))

Still array entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[hdf]

Ron has already given you an explanation. One way around it is to use an
IF() statement with a very large numbers as its FALSE element:

=SUM(SMALL(IF(B1:B6=1,A1:A6,1E+307),ROW(INDIRECT("1:2"))))

Still array entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Thanks all of you for your help.

 

[Ron Rosenfeld]

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.

Your thinking that the multiplication operator should be interpreted as an
addition operator is incorrect, as is your analysis of how your LARGE function
is working.

I don't know how to be any clearer than to repeat what I said, but put it all
into this message:

Here is what is happening with your LARGE formula:

=SUM(LARGE((TARGET=1)*TABLE,ROW(INDIRECT("1:2"))))

Again:

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 largest numbers in that array are 3 and 2.

The sum of 3 and 2 is 5

---------------------------------------------

With your SMALL formula, exactly the same thing, up to the last step:

=SUM(SMALL((TARGET=1)*TABLE,ROW(INDIRECT("1:2"))))

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 0 and 0.

The sum of 0 and 0 is 0
--ron

 

[Ron Rosenfeld]

Ron has already given you an explanation. One way around it is to use an
IF() statement with a very large numbers as its FALSE element:

=SUM(SMALL(IF(B1:B6=1,A1:A6,1E+307),ROW(INDIRECT("1:2"))))

Still array entered.

Note that your 1E+307 factor is superfluous. My previously posted solution:

=SUM(SMALL(IF(TARGET=1,TABLE),{1,2}))

is shorter. SUM ignores logical values in an array or reference.
--ron

 

[Ron Rosenfeld]

Your thinking that the multiplication operator should be interpreted as an
addition operator is incorrect

That statement is incorrect.

You wrote that you thought the "*" should be interpreted as an AND. In fact,
it is (kind of) being interpreted that way. Where you were incorrect was in
equating the AND operation to ADDITION (summing). In point of fact, an AND
argument equates to multiplication. For AND to be true, both arguments must be
true. So, for example:


expr1 expr2 result
0 0 0
0 1 0
1 0 0
1 1 1

As you can see, result is the the result of expr1 * expr2

--ron

 

[hdf]

That statement is incorrect.

You wrote that you thought the "*" should be interpreted as an AND. In fact,
it is (kind of) being interpreted that way. Where you were incorrect was in
equating the AND operation to ADDITION (summing). In point of fact, an AND
argument equates to multiplication. For AND to be true, both arguments must be
true. So, for example:

expr1 expr2 result
0 0 0
0 1 0
1 0 0
1 1 1

As you can see, result is the the result of expr1 * expr2

--ron

Ron, thanks for taking the time to clear it up - I understand now.

Hector

 

[Ron Rosenfeld]

Ron, thanks for taking the time to clear it up - I understand now.

Hector

You're welcome.
--ron

 

[Sandy Mann]

Note that your 1E+307 factor is superfluous. My previously posted

solution:

Sorry Ron, I didn't scroll down far enough in that post to see that you had
posted it otherwise I would not have offered mine. As my mother used to
say, "there are none so blond as those that think thgat they already know"

But am glad that I did post it because otherwise you would not have
reminded me that SUM() ignores logical values

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk