Minimun values in a list

[edwgolz]

I have an array of data in which I'd like to return the sum of the lowest 10
numbers without having to sort. Specifically, I would like to find the lowest
10 cells 1-20, 2-21, 3-22 etc in a list of 100.
Thanks

 

[John C]

If you are wanting the sum of the 10 lowest values in a range:
=SUM(SMALL(A1:A100,ROW(1:10)))
This is an **array** formula, you must CTRL+SHIFT+ENTER to enter the
formula. Was confused by what you meant 1-20, 2-21, 3-22, etc. So if this
formula doesn't do what you asked, please clarify.

 

[Glenn]

I have an array of data in which I'd like to return the sum of the lowest 10
numbers without having to sort. Specifically, I would like to find the lowest
10 cells 1-20, 2-21, 3-22 etc in a list of 100.
Thanks

Assuming your data is in column A starting in row 1, array enter
(CTRL+SHIFT+ENTER) the following:

=SUM(SMALL(A1:A20,{1,2,3,4,5,6,7,8,9,10}))

Copy down as needed.

 

[Rick Rothstein]

Your formula does not need to be array-entered... just committing it with
the ENTER key by itself should work.

 

[Glenn]

Your formula does not need to be array-entered... just committing it
with the ENTER key by itself should work.

You're right. I was looking at a solution similar to John C's, but noticed that
when you copied it down the column, which I think the OP needs to do, the
numbers within the ROW() function also increment, changing the result. So using
the array constant fixed that problem and I didn't think to check if it still
needed to be array-entered.

 

[Gord Dibben]

Please remember, if your question is answered, to check the YES box below.
It helps everyone.

Doesn't help anyone who uses a news reader John.

We don't need a "Yes" in the box below...............we download all posts
and can see all replies.


Gord Dibben MS Excel MVP

 

[John C]

so in your newsreader, is there no indication that a question has been
answered? If that is the case, why would you use a newsreader if you have to
read every post to see if a question has been satisfactorily answered?

 

[Rick Rothstein]

Newsreader allow you to view messages by threads (subjects) in a tree
view... unread messages have the title shown in bold text, read messages
shown in non-bold text. Not only can you tell if a message has a response
but, in longer threads, you can see which messages you have already read and
which are still waiting for you to be read. Also, clicking on the message
header shows the message in another panel without all the webpage flipping
that I have seen in forums.

 

[John C]

So, other than reading the responses, there is no quick clear cut way to know
if something has been resolved? Sounds like a bad idea to me.

 

[Pete_UK]

I tend to use Google Groups (and a newsreader when that is playing up,
which seems to be quite often recently). The system in GG is 5 stars,
so again the comment about clicking Yes is irrelevant - though I've
seen quite a few posts that have such a comment.

Pete

 

[John C]

Well, I've changed it up a little bit. Remember though, there are alot of
people who access the MS Office community through *gasp*, MS website. I know,
such a thought .

 

[Rick Rothstein]

If you care about the thread, then you probably have been reading all the
messages in it, so when the OP says "Thanks, that solved my problem", you
can figure the problem was resolved. Another thing about using a newsreader
is that you can easily select an whole group of messages (whether in the
same thread or not) and mark them all "read" all at once so their headers
don't show in bold. You can also have the newsreader just show you messages
that you have read yet. All in all, I find it quite flexible to work with
(especially as a volunteer answering questions on newsgroups).

 

[John C]

To each their own. I think far less people use the newsreaders than you
think. Don't get me wrong, I am sure plenty do. But I am equally sure that
more people use the MS Community. Have a wonderful day

 

[edwgolz]

Wow. Had a question and got in the middle of an argument. Thanks for the
response. What I'm trying to do is find the sum of the lowest 10 values of 20
entries, starting with the first 20 in the list(1 through 20). Then move down
the list by 1 cell (2 through 21, 3-22 etc until i reach the end of the data
field. I attempted copying your formula into the sheet and it returned only
the lowest 1 value. I'm not sure how to execute "ctrl-shift-enter". Thanks
for your help. Rick, Pete and Gord can straighten me out on news readers
later

 

[Gord Dibben]

What Rick said.................<g>


Gord