adding cells within an array

[Guest]

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.

 

[Bob Phillips]

Put this in B1

=IF(OR(A1=0,A2<>0),"",SUM(INDEX($A$1:A1,MAX(IF($A$1:A1=0,ROW($A$1:A1)))):A2)
)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy down.

--
HTH

Bob Phillips

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

 

[Bernard Liengme]

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

 

[Guest]

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?

 

[Bob Phillips]

Take a look at

http://cjoint.com/?izblt21fQ5

--
HTH

Bob Phillips

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

 

[Guest]

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]

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)

 

[Guest]

Thanks for the explanation Bob. I just have one more question. Do you know
of a way that excel can erase the blank cells between filled cells in a
column? I'm now trying to have all the pieces of data in consecutive cells.
I can even do this in a new spreadsheet if need be.

Thanks!

 

[Bob Phillips]

Do you mean you want to shunt the data up, remove the blanks?

If so, you could use Data>Filter>Advanced Filter to copy it to another
location with a criteria value of <>.

--
HTH

Bob Phillips

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

 

[Guest]

Yes, exactly. I just want to remove the blanks from the column. I tried
using the advanced filter but I do not know what to use for the criteria
value. Any suggestions?

 

[Guest]

Hey, actually I was tooling around and I just figured it out. I went to Data
-> Filter->Autofilter and then selected 'non-blanks' from the drop down menu.
Thanks anyway.

 

[Bob Phillips]

That filters them, so it shows non-blanks, but the list still contains the
blanks. That may or may not be what you want.

--
HTH

Bob Phillips

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