1. excel column puzzle

1.
I have a large column in excel 2000. I want to count the empty cells inbetween the cells with numbers and display the totals.

2.
I also need to count the empty cells starting from EOF and display this total.

2. Re: excel column puzzle

I'm not sure I understand exactly what you're looking to do but see if this will help.
=countblank(range)

Whats EOF?

3. Re: excel column puzzle

EOF=end of file?

4. Re: excel column puzzle

I have spent many hours, days, weeks on this problem without success.
I want to display only the totals for the number of empty cells that appear between cells that contain data.
For-example, The column of cells might contain the following series of values, 20010002000100001120000011000002110000000200000100 0021100.
(0 = empty cell)
Therefore the displayed totals would be,233455754.

The last two 00 represent records that do not put any data into the cells. Also they are not between cells with entries and this is my next problem.
These two empty cells must be counted and the total (which is 2) is to be displayed separate to the others because they are not closed of.

does this help at all?

"EoF" refers to "end of file function" which returns false until the end of the file has been reached.

Any ideas capt Jimbo?

santae

6. Re: Capt Jimbo/robo

Here's a slightly awkward way of summing the rows of zeros between non-zero numbers.
This requires three columns immediately to the right of the 'data column'. I have used "RC" cell referencing (turn on from Tools/Options/General Tab - check R1C1 reference style). In the first column to the right of the data enter:

=IF(ISBLANK(C[-1]),1,0) and copy down for each row of data. This will return a "1" for each 'zero' data.

In the second column enter:
=IF((C[-1]),R[-1]+C[-1],0) and copy down all rows
This will sum groups of "1s" in the previous column.
In the third column enter:

=IF(R[1]C[-1]<RC[-1],RC[-1],"") and copy down all rows
This will return the highest value of each group of zeros.

If you uncheck the R1C1 reference method, once the formulas are entered, it will be easier to understand the formulas.

7. Re: Capt Jimbo/robo

You could of course use a single formula combining all three - but it would be quite difficult to understand ! :|

The reason the formulas are presented in relative referencing (RC) style, is that they can be placed anywhere in your example without adjustment for the absolute location of the data.

8. Re: Capt Jimbo/robo

I have excel97 and in the functions, under statistical there is a function calld countblank that counts how many blank cells in a range. Sounds like what you are after.

9. Re: Capt Jimbo/robo

The problem is, though, to count the individual groups of blanks in a column of data - not just the total number of blanks. :|

10. Re: excel column puzzle

Not sure but this may give you what you want. This is a macro that you run... if your not sure how to put it into Excel let me know.

Sub FindBlank()
Dim i As Integer
Dim rng As Range
Dim cell
Dim count As Integer
Dim YeTotals As String

'Initialize variables
i = 1
Set rng = Range(Range("a1"), Range("a" & Range("a65536").End(xlUp).Row))
count = 0

'count # of blank cells between non-blank cells in range
For Each cell In rng
If cell.Value = "" Then
count = count + 1
End If

If cell.Offset(1, 0).Value <> "" Then
YeTotals = YeTotals & "," & count
count = 0
End If
Next cell
MsgBox "blanks total is " & YeTotals

End Sub

Page 1 of 3 123 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•