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.