Home 
Search 
Today's Posts 
#1




Problem with small()....I think
Ok, A few weeks ago I asked for help in finding the smallest two numbers in a
group of numbers that were added together in order to reduce the total by the smallest numbers. the form that was suggested was(using my cell locations): =sum(I9,L9,R9,V9,Y9small((I9,L9,R9,V9,Y9),{1,2})) This is not working correctly, assume that the the values in the designated cells are the following values:0, 11, 9, 9, 15; the value that is computed is 50. The only thing that seems to be consistent is that the last value is added in again then the two smallest values are subtracted. I tried on several sets of values and it was always coming up the same way, if I add the last value in then subtract the two lowest values it matches what Excel computes. Obviously I am doing something wrong but what? All comments, and suggestions are welcome and appreciated. 
#3




Another way:
=SUM(I9,L9,R9,V9,Y9)SUM(SMALL((I9,L9,R9,V9,Y9),{1,2})) Biff "William" wrote in message ... Hi Try =SUM(I9,L9,R9,V9,Y9)SMALL((I9,L9,R9,V9,Y9),1)SMALL((I9,L9,R9,V9,Y9),2)  XL2003 Regards William "Weekend user" wrote in message ... Ok, A few weeks ago I asked for help in finding the smallest two numbers in a group of numbers that were added together in order to reduce the total by the smallest numbers. the form that was suggested was(using my cell locations): =sum(I9,L9,R9,V9,Y9small((I9,L9,R9,V9,Y9),{1,2})) This is not working correctly, assume that the the values in the designated cells are the following values:0, 11, 9, 9, 15; the value that is computed is 50. The only thing that seems to be consistent is that the last value is added in again then the two smallest values are subtracted. I tried on several sets of values and it was always coming up the same way, if I add the last value in then subtract the two lowest values it matches what Excel computes. Obviously I am doing something wrong but what? All comments, and suggestions are welcome and appreciated. 
#4




Or maybe even this:
=SUM(LARGE((I9,L9,R9,V9,Y9),{1,2,3})) Biff "Biff" wrote in message ... Another way: =SUM(I9,L9,R9,V9,Y9)SUM(SMALL((I9,L9,R9,V9,Y9),{1,2})) Biff "William" wrote in message ... Hi Try =SUM(I9,L9,R9,V9,Y9)SMALL((I9,L9,R9,V9,Y9),1)SMALL((I9,L9,R9,V9,Y9),2)  XL2003 Regards William "Weekend user" wrote in message ... Ok, A few weeks ago I asked for help in finding the smallest two numbers in a group of numbers that were added together in order to reduce the total by the smallest numbers. the form that was suggested was(using my cell locations): =sum(I9,L9,R9,V9,Y9small((I9,L9,R9,V9,Y9),{1,2})) This is not working correctly, assume that the the values in the designated cells are the following values:0, 11, 9, 9, 15; the value that is computed is 50. The only thing that seems to be consistent is that the last value is added in again then the two smallest values are subtracted. I tried on several sets of values and it was always coming up the same way, if I add the last value in then subtract the two lowest values it matches what Excel computes. Obviously I am doing something wrong but what? All comments, and suggestions are welcome and appreciated. 
#5




"Weekend user" wrote...
Ok, A few weeks ago I asked for help in finding the smallest two numbers in a group of numbers that were added together in order to reduce the total by the smallest numbers. the form that was suggested was(using my cell locations): =sum(I9,L9,R9,V9,Y9small((I9,L9,R9,V9,Y9),{1,2})) .... The reason this doesn't work is because you're subtracting an array from the single cell Y9, which effectively doublecounts Y9 in the sum. With I9=0, L9=11, R9=9, V9=9 and Y9=15, your sum becomes SUM(0,11,9,9,15{0,9}) = SUM(0,11,9,9,{15,6}) = SUM(0,11,9,9,15,6) = 50 Try =SUM((I9,L9,R9,V9,Y9),SMALL((I9,L9,R9,V9,Y9),{1,2})) 
#6




On Sun, 15 May 2005 20:28:01 0700, Weekend user
wrote: Ok, A few weeks ago I asked for help in finding the smallest two numbers in a group of numbers that were added together in order to reduce the total by the smallest numbers. the form that was suggested was(using my cell locations): =sum(I9,L9,R9,V9,Y9small((I9,L9,R9,V9,Y9),{1,2})) This is not working correctly, assume that the the values in the designated cells are the following values:0, 11, 9, 9, 15; the value that is computed is 50. The only thing that seems to be consistent is that the last value is added in again then the two smallest values are subtracted. I tried on several sets of values and it was always coming up the same way, if I add the last value in then subtract the two lowest values it matches what Excel computes. Obviously I am doing something wrong but what? All comments, and suggestions are welcome and appreciated. It's close. But if you use the Evaluate Formula button (Tools/Formula Auditing/Evaluate Formula) you will see that your formula is not doing what you expect. Try this slight modification, instead: =SUM(I9,L9,R9,V9,Y9)SUM(SMALL((I9,L9,R9,V9,Y9),{1,2})) ron 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel Display Problem  Excel Discussion (Misc queries)  
data structure and my problem  Excel Worksheet Functions  
Problem opening Excel 2003 in a Small Business edition  Excel Discussion (Misc queries)  
Freeze Pane problem in shared workbooks  Excel Discussion (Misc queries)  
Row Autofit problem Excel 2003  Excel Discussion (Misc queries) 