Re: adding cells within an array

Take a look at

http://cjoint.com/?izblt21fQ5

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"beecher" <beecher@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8249C5BC-77BC-4973-8532-45140003EF51@xxxxxxxxxxxxxxxx
Thanks for the help fellas. I'm still having some trouble though. When I
tried Bob's formula I only got blank cells. When I tried Bernard's
formula,
I received a column of zeroes instead of the sums of each group.

Maybe I should clarify the situation. Each of the numbers (0 or 1.2 or
1.3
etc.) are in a column. There are around 1400 cells in the column. Within
each column there are alternating strings of zeroes and non-zero numbers.
For instance, part of the column A1:A20 could read like this 0, 0, 0, 1.3,
1.2, 1.3, 1.3, 1.3, 1.3, 1.3, 0, 0, 0, 0, 0, 0, 1.1, 1.8, 1.5, 0. I call
each string of non-zero numbers groups. Would you happen to know a
formula
that provides the sum for each individual groups in the column?

"Bernard Liengme" wrote:

Not very fancy but here goes.
Assuming you data is in A1:A(whatever)
In B1 enter =IF(AND(A1<>0,A2=0),SUM($A$1:A1),"") and copy down the
column
IN C2 (NOT C1) enter =IF(ISERROR(B2-SUM($B$1:B1)),"",B2-SUM($B$1:B1))
and
copy down the column
Hide column B if required
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"beecher" <beecher@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A11C22F9-7132-42B3-A3B6-6CC38E83F61C@xxxxxxxxxxxxxxxx
I'm trying to devise a way to add cells within an array following a
set of
criteria.
I have an array with a series of zero and non-zero numbers. As an
example:

1 0
2 0
3 1.2
4 1.3
5 1.4
6 0
7 1.1
8 1.8
9 0
10 0

I want to add all the numbers that are between the zeroes. That is, I
would
like to have two separate values in the above example: one value that
is
the
sum of 1.2, 1.3, and 1.4 and a second value that equals the sum of
1.1+1.8.
I could manually go through the array and add each sum, but because
there
are
nearly 40 sums in each array and multiple arrays to do this
calculation I
would like to find a function to perform this task for me. Any help
would
be
much appreciated.





.

Relevant Pages

  • Re: adding cells within an array
    ... Thanks for the excel doc. ... each column there are alternating strings of zeroes and non-zero numbers. ... I have an array with a series of zero and non-zero numbers. ... sum of 1.2, 1.3, and 1.4 and a second value that equals the sum of ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Subset Sum problem (w/ limited scope)
    ... The "Subset Sum" problem (stated various ways depending on what you ... a subset of those integers that sums up to zero. ... Are there going to be duplicate values in your original array? ...
    (comp.lang.fortran)
  • Re: How can I find the greatest possible sum within 12 months? A newbie...
    ... It may be because you have headers, and when I tied it first I omitted ... You may have omitted to enter it as an array formula (see notes on ... the starting sum is in A2 and the end sum is in A123. ... Excel will insert them for you. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Explanation of SUMPRODUCT
    ... I don't even think you need to enter it as an array ... Yes, we know this, but in this case SUM is an array formula SUMPRODUCT ... > each element is the number of times the corresponding cell value appears ...
    (microsoft.public.excel.programming)
  • Re: Simple but confusing algorith question
    ... As you mention that this is an interview question ... ... through the array, then query against the structure somehow. ... structure and the cost of querying the data structure. ... array 1,2,3,4 sum = 5 ...
    (microsoft.public.dotnet.languages.csharp)