Page 1 of 3 123 LastLast
Results 1 to 10 of 22
  1. #1

    Default excel column puzzle

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

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

    Is there any suggestions please.

  2. #2
    Capt Jimbo

    Default Re: excel column puzzle

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

    Whats EOF?

  3. #3

    Default Re: excel column puzzle

    EOF=end of file?

  4. #4

    Default Re: excel column puzzle

    Thankyou captain jimbo for your reply and thoughts.

    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?

  5. #5

    Default Re: Capt Jimbo/robo

    Thankyou for your replys and thoughts.
    Please look under tawhiti for more information on my excel spreadsheet puzzle.


  6. #6

    Default 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. #7

    Default 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. #8

    Default 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. #9

    Default 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. #10

    Default 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

Similar Threads

  1. transforming excel row to column
    By bpt2 in forum PressF1
    Replies: 8
    Last Post: 25-10-2006, 02:59 PM
  2. Replies: 3
    Last Post: 28-01-2005, 09:53 PM
  3. Replies: 1
    Last Post: 18-11-2002, 09:23 AM
  4. Replies: 0
    Last Post: 02-09-1999, 01:21 AM
  5. Replies: 0
    Last Post: 09-10-1998, 11:19 PM

Posting Permissions

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