Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Empty Cells of a Range
Hello all, I have a question regarding the count functions with excel. I am wondering how to count sets of "unique" empty cells within a range? Take the example below: [image: http://doggpoundclan.com/images/excelexample01.jpg] Within a row, I would like a count of how many instances of "2 empty cells in a row", and how many instances of "3 empty cells in a row", and how many instances of "4 empty cells in a row", etc, etc. So in the first row, there are 2 instances of "2 empty cells in a row", 1 instance of "3 empty cells in a row", and 1 instance of "5 empty cells in a row". The second row has 3 instances of "2 empty cells in a row", and 2 instances of "3 empty cells in a row". How would I go about counting all the sets of empty cells and defining them in a table? -- IronDogg ------------------------------------------------------------------------ IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291 View this thread: http://www.excelforum.com/showthread...hreadid=531171 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Empty Cells of a Range
It isn't clear to me how you got your counts based on the picture you
referenced. So you would have to explain that. Atre cells counted more than once and gray cells excluded? do you want to do this with a macro or with worksheet formulas Do you want counts by row or a sum over all rows. -- Regards, Tom Ogilvy "IronDogg" wrote in message ... Hello all, I have a question regarding the count functions with excel. I am wondering how to count sets of "unique" empty cells within a range? Take the example below: [image: http://doggpoundclan.com/images/excelexample01.jpg] Within a row, I would like a count of how many instances of "2 empty cells in a row", and how many instances of "3 empty cells in a row", and how many instances of "4 empty cells in a row", etc, etc. So in the first row, there are 2 instances of "2 empty cells in a row", 1 instance of "3 empty cells in a row", and 1 instance of "5 empty cells in a row". The second row has 3 instances of "2 empty cells in a row", and 2 instances of "3 empty cells in a row". How would I go about counting all the sets of empty cells and defining them in a table? -- IronDogg ------------------------------------------------------------------------ IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291 View this thread: http://www.excelforum.com/showthread...hreadid=531171 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Empty Cells of a Range
Tom Ogilvy Wrote: It isn't clear to me how you got your counts based on the picture you referenced. So you would have to explain that. Atre cells counted more than once and gray cells excluded? do you want to do this with a macro or with worksheet formulas Do you want counts by row or a sum over all rows. -- Regards, Tom Ogilvy Okay, the picture shows a work shift schedule, with a Sunday through Saturday work week. The gray cells are Saturday, Sunday. D=Days, N=Nights, M=mornings. Cells with nothing in them are days off. What I am looking for is a formula at the end of each row that will calculate how many "2 Day off"-sets of days off there were in an entire years shift. Then another cell with a formula showing how many "3 Days off"-sets of days off there were in an entire years shift, then 4 days, then 5 days, then any sets larger than 5 days... Gray cells are included in counts. Does that help explain my problem better? -- IronDogg ------------------------------------------------------------------------ IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291 View this thread: http://www.excelforum.com/showthread...hreadid=531171 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Empty Cells of a Range
Your first problem is that Excel only has 256 columns, so it won't support
365/6 days. anyway, SUM(1*((LARGE(IF((D8:L8="")*(E8:M8<D8:L8),COLUMN( D8:L8),0),{1,2,3,4,5,6,7,8 ,9})-LARGE(IF((E8:M8="")*(E8:M8<D8:L8),COLUMN(E8:M8),1 ),{1,2,3,4,5,6,7,8,9} )+1)=2)) entered with Ctrl+Shift+Enter should indicate the number of 2 successive blanks in the range D8:M8. I think you have to have a non empty cell at each end so you might need to put in a dummy column at each end and fill it with a value. the arrays 1,2,3,4,5,6,7,8,9 must be numbered high enough to cover the total number of blank ranges in the row. D8:M8 was just a testing range. You can adjust if for a larger section of a row by using a similar pattern. -- Regards, Tom Ogilvy "IronDogg" wrote in message ... Tom Ogilvy Wrote: It isn't clear to me how you got your counts based on the picture you referenced. So you would have to explain that. Atre cells counted more than once and gray cells excluded? do you want to do this with a macro or with worksheet formulas Do you want counts by row or a sum over all rows. -- Regards, Tom Ogilvy Okay, the picture shows a work shift schedule, with a Sunday through Saturday work week. The gray cells are Saturday, Sunday. D=Days, N=Nights, M=mornings. Cells with nothing in them are days off. What I am looking for is a formula at the end of each row that will calculate how many "2 Day off"-sets of days off there were in an entire years shift. Then another cell with a formula showing how many "3 Days off"-sets of days off there were in an entire years shift, then 4 days, then 5 days, then any sets larger than 5 days... Gray cells are included in counts. Does that help explain my problem better? -- IronDogg ------------------------------------------------------------------------ IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291 View this thread: http://www.excelforum.com/showthread...hreadid=531171 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Empty Cells of a Range
Assuming this is a rota, could you not fill in the blanks with 'O' for Off,
for example, then count the O's using And offset reference capabilities? "IronDogg" wrote: Hello all, I have a question regarding the count functions with excel. I am wondering how to count sets of "unique" empty cells within a range? Take the example below: [image: http://doggpoundclan.com/images/excelexample01.jpg] Within a row, I would like a count of how many instances of "2 empty cells in a row", and how many instances of "3 empty cells in a row", and how many instances of "4 empty cells in a row", etc, etc. So in the first row, there are 2 instances of "2 empty cells in a row", 1 instance of "3 empty cells in a row", and 1 instance of "5 empty cells in a row". The second row has 3 instances of "2 empty cells in a row", and 2 instances of "3 empty cells in a row". How would I go about counting all the sets of empty cells and defining them in a table? -- IronDogg ------------------------------------------------------------------------ IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291 View this thread: http://www.excelforum.com/showthread...hreadid=531171 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Empty Cells of a Range
Newbie Wrote: Assuming this is a rota, could you not fill in the blanks with 'O' fo Off, for example, then count the O's using And offset referenc capabilities? Yes, that is kinda what the above formula does already, except i counts blanks instead of O's. I already use a formula: =COUNTIF(D5:FA5 "N") to count number of nights, etc. So with the "O's", it is not jus the number of "O's", but rather the number of groups of JUST concurrent "O's", nothing more. So in the end, wouldn't the formula b very similiar to the one Tom provided? Or am I missing something there regarding the offset reference capabilities? Also, to do that way, I would prefer if the "O's" could be invisible t the final printed product. Less "busy" that way, maybe... : -- IronDog ----------------------------------------------------------------------- IronDogg's Profile: http://www.excelforum.com/member.php...fo&userid=3329 View this thread: http://www.excelforum.com/showthread.php?threadid=53117 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique text/number cells from a range | Excel Discussion (Misc queries) | |||
Counting unique text/number cells from a range | Excel Programming | |||
Counting unique text/number cells from a range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |