Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hdf hdf is offline
external usenet poster
 
Posts: 30
Default SMALL function seems not to work correctly

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 se****ial 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?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default SMALL function seems not to work correctly

On Fri, 15 Feb 2008 11:33:01 -0800 (PST), hdf wrote:

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 se****ial 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hdf hdf is offline
external usenet poster
 
Posts: 30
Default SMALL function seems not to work correctly

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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default SMALL function seems not to work correctly

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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hdf hdf is offline
external usenet poster
 
Posts: 30
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default SMALL function seems not to work correctly

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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,345
Default SMALL function seems not to work correctly

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


Replace @mailinator.com with @tiscali.co.uk


"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



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hdf hdf is offline
external usenet poster
 
Posts: 30
Default SMALL function seems not to work correctly

On Feb 15, 10:57 pm, "Sandy Mann" wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"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


Thanks all of you for your help.
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default SMALL function seems not to work correctly

On Fri, 15 Feb 2008 13:30:10 -0800 (PST), hdf wrote:

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
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default SMALL function seems not to work correctly

On Fri, 15 Feb 2008 21:57:53 -0000, "Sandy Mann"
wrote:

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


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default SMALL function seems not to work correctly

On Fri, 15 Feb 2008 19:16:47 -0500, Ron Rosenfeld
wrote:

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
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hdf hdf is offline
external usenet poster
 
Posts: 30
Default SMALL function seems not to work correctly

On Feb 16, 1:33 am, Ron Rosenfeld wrote:
On Fri, 15 Feb 2008 19:16:47 -0500, Ron Rosenfeld
wrote:

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


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

Hector
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default SMALL function seems not to work correctly

On Fri, 15 Feb 2008 17:45:32 -0800 (PST), hdf wrote:

On Feb 16, 1:33 am, Ron Rosenfeld wrote:
On Fri, 15 Feb 2008 19:16:47 -0500, Ron Rosenfeld
wrote:

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


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

Hector


You're welcome.
--ron
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,345
Default SMALL function seems not to work correctly

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


Replace @mailinator.com with @tiscali.co.uk


"Ron Rosenfeld" wrote in message
...
On Fri, 15 Feb 2008 21:57:53 -0000, "Sandy Mann"

wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Small game wont work in Excel 2007 Phil Excel Discussion (Misc queries) 4 April 3rd 07 10:24 AM
Sorting numbers doesn't work correctly GrammyEmmy New Users to Excel 8 June 25th 06 11:45 PM
How do I get Auto-Fit to work correctly? Mickey Dunne Excel Worksheet Functions 0 May 11th 06 01:47 AM
custom filter does not work correctly RJ Excel Discussion (Misc queries) 1 September 9th 05 07:34 PM
office 97 small business edition, excel does not work on xp suncityart Setting up and Configuration of Excel 2 July 13th 05 02:20 AM


All times are GMT +1. The time now is 09:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"