Problem with small()....I think

W

Weekend user

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.
 
W

William

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
(e-mail address removed)
 
H

Harlan Grove

Weekend user said:
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}))
 
R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top