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

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

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?

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

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.