Forum Home
Press F1
Thread ID: 27001	2002-11-10 22:23:00	excel column puzzle	santae (2502)	Press F1

Post ID	Timestamp	Content	User
97643	2002-11-10 22:23:00	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. Is there any suggestions please.	santae (2502)
97644	2002-11-10 23:12:00	I'm not sure I understand exactly what you're looking to do but see if this will help. =countblank(range) Whats EOF?	Capt Jimbo (17)
97645	2002-11-11 05:13:00	EOF=end of file?	robo (205)
97646	2002-11-12 08:59:00	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?	tawhiti (2503)
97647	2002-11-12 09:05:00	Thankyou for your replys and thoughts. Please look under tawhiti for more information on my excel spreadsheet puzzle. santae	tawhiti (2503)
97648	2002-11-12 10:05:00	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.	wuppo (41)
97649	2002-11-12 10:17:00	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.	wuppo (41)
97650	2002-11-12 10:39:00	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.	wotz (335)
97651	2002-11-12 10:44:00	The problem is, though, to count the individual groups of blanks in a column of data - not just the total number of blanks. :|	wuppo (41)
97652	2002-11-13 04:00:00	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	parry (27)
1 2 3