Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Small game wont work in Excel 2007 | Excel Discussion (Misc queries) | |||
Sorting numbers doesn't work correctly | New Users to Excel | |||
How do I get Auto-Fit to work correctly? | Excel Worksheet Functions | |||
custom filter does not work correctly | Excel Discussion (Misc queries) | |||
office 97 small business edition, excel does not work on xp | Setting up and Configuration of Excel |