Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 16th 05, 04:28 AM
Weekend user
 
Posts: n/a
Default 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.

  #5   Report Post  
Old May 16th 05, 08:42 AM
Harlan Grove
 
Posts: n/a
Default

"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}))




  #6   Report Post  
Old May 16th 05, 11:21 AM
Ron Rosenfeld
 
Posts: n/a
Default

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


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
Excel Display Problem Bill Martin -- (Remove NOSPAM from address) Excel Discussion (Misc queries) 0 April 19th 05 05:25 PM
data structure and my problem samantha Excel Worksheet Functions 2 April 11th 05 02:38 PM
Problem opening Excel 2003 in a Small Business edition installing:function of Excel Excel Discussion (Misc queries) 1 April 11th 05 09:09 AM
Freeze Pane problem in shared workbooks JM Excel Discussion (Misc queries) 1 February 1st 05 12:04 AM
Row Autofit problem Excel 2003 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.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017