Re: adding cells within an array

It is not testing the column to be zero, but rather each and every cell in
$A$1:A1 to be 0. For each that is, it returns the row number of that
particular cell. This is so that I can build an array of 0 row numbers wand
extract the last one by using MAX. I then use that as part of the cells to
SUM.

By the way, $A$1:A1 may seem nonsense, why not just say A1, but in the
second line the formula changes to $A$1:A2, etc.

--
HTH

Bob Phillips

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

"beecher" <beecher@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3C091820-CB1D-4B1A-ABEB-78DFE9FB79FF@xxxxxxxxxxxxxxxx
Hey Bob,

Thanks for the excel doc. It was really helpful and exactly what I
needed.
I'm now just trying to understand the formula. What does the part of the
formula that reads IF($A$1:A1=0, Row($A$1:A1)) mean? I think it means
that
if the column A1 through the adjacent cell is equal to zero, then return
the
row number in the column corresponding to the reference. But, what does
it
mean for a column to be equal to zero and how does the program determine
the
row number of the reference? Thank you for your patience with someone not
used to working in excel.

"Bob Phillips" wrote:

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: 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: 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: 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: 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: Excel 2003 Problem
    ... result is zero, as if it does not recognize the data as numerical. ... cell, it recognizes the new data as a number, and cell C is ... and their sum won't change. ... If you're summing them as =SUM, then Excel would treat both ...
    (microsoft.public.office.misc)