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

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.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"edwgolz" wrote:

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

edwgolz wrote:
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.

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

--
Rick (MVP - Excel)


"Glenn" wrote in message
...
edwgolz wrote:
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 wrote:
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.

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial wrote:

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

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?
--
** John C **

"Gord Dibben" wrote:

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial wrote:

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

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.

--
Rick (MVP - Excel)


"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **

"Gord Dibben" wrote:

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial wrote:

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

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.
--
** John C **

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **

"Gord Dibben" wrote:

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial wrote:

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

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

On Oct 24, 4:50*pm, John C <johnc@stateofdenial wrote:
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.
--
** John C **



"Rick Rothstein" wrote:
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.

--
Rick (MVP - Excel)

"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **

"Gord Dibben" wrote:

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial wrote:

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- Hide quoted text -

- Show quoted text -

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 :).
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Pete_UK" wrote:

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

On Oct 24, 4:50 pm, John C <johnc@stateofdenial wrote:
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.
--
** John C **



"Rick Rothstein" wrote:
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.

--
Rick (MVP - Excel)

"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **

"Gord Dibben" wrote:

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial wrote:

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- Hide quoted text -

- Show quoted text -

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

--
Rick (MVP - Excel)


"John C" <johnc@stateofdenial wrote in message
...
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.
--
** John C **

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **

"Gord Dibben" wrote:

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial
wrote:

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

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 :)
--
** John C **

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"John C" <johnc@stateofdenial wrote in message
...
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.
--
** John C **

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"John C" <johnc@stateofdenial wrote in message
...
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?
--
** John C **

"Gord Dibben" wrote:

On Thu, 23 Oct 2008 07:18:13 -0700, John C <johnc@stateofdenial
wrote:

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

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 :)

"John C" wrote:

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.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"edwgolz" wrote:

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

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


Gord

On Fri, 24 Oct 2008 04:41:01 -0700, John C <johnc@stateofdenial wrote:

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?