adding cells within an array
 

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.

adding cells within an array
 

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)

"beecher" wrote in message
...
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.

adding cells within an array
 

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

adding cells within an array
 

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

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

adding cells within an array
 

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

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

adding cells within an array
 

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" wrote:

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

adding cells within an array
 

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

If so, you could use DataFilterAdvanced 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)

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

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

adding cells within an array
 

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?

"Bob Phillips" wrote:

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

If so, you could use DataFilterAdvanced 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)

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

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

adding cells within an array
 

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.

"beecher" wrote:

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?

"Bob Phillips" wrote:

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

If so, you could use DataFilterAdvanced 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)

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

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

adding cells within an array
 

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)

"beecher" wrote in message
...
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.

"beecher" wrote:

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?

"Bob Phillips" wrote:

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

If so, you could use DataFilterAdvanced 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)

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

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