Remember Me?

 Weekend user Posts: n/a 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,Y9-small((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.

 Biff Posts: n/a Harlan Grove Posts: n/a "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,Y9-small((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 double-counts 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}))

 Ron Rosenfeld Posts: n/a 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,Y9-small((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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Bill Martin -- (Remove NOSPAM from address) Excel Discussion (Misc queries) 0 April 19th 05 05:25 PM samantha Excel Worksheet Functions 2 April 11th 05 02:38 PM installing:function of Excel Excel Discussion (Misc queries) 1 April 11th 05 09:09 AM JM Excel Discussion (Misc queries) 1 February 1st 05 12:04 AM Matthias Klaey Excel Discussion (Misc queries) 0 January 19th 05 05:33 PM

All times are GMT +1. The time now is 12:20 AM. Copyright ©2004-2019 ExcelBanter.