Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to count the highest number of equal cells
I got a worksheet with only 2 different numbers in each cell like this:
1 1 1 0 0 1 0 0 1 1 1 My question are how do I count the largest number of equal cells... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to count the highest number of equal cells
=FREQUENCY(A1:A13,A1:A13)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "fiur" wrote in message ... I got a worksheet with only 2 different numbers in each cell like this: 1 1 1 0 0 1 0 0 1 1 1 My question are how do I count the largest number of equal cells... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to count the highest number of equal cells
thanx alot for your tip.... but I need to be more spesific...... lets say I
got 4 cells in a row with 1 then two cells with 0 then I must start to count over again.... I only wanna count the larges number of equal cells in a row (1-1-1-1-0-1-0-) the result will be: number 1= 4 number 0= 1 Hopefully this will explain my problem better..... "Bob Phillips" wrote: =FREQUENCY(A1:A13,A1:A13) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "fiur" wrote in message ... I got a worksheet with only 2 different numbers in each cell like this: 1 1 1 0 0 1 0 0 1 1 1 My question are how do I count the largest number of equal cells... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to count the highest number of equal cells
Use a helper column. Assume your numbers are in the range A1:A11 (assuming
no empty cells within the range). Enter 1 in B1. Enter this formula in B2 and copy down to B11: =IF(A2=A1,B1+1,1) Then, to get the highest number of consecutive cells: =MAX(B2:B11) If you need to break it out by specific number, eg: max for 1 and max for 0: =SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11)) =SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11)) Biff "fiur" wrote in message ... thanx alot for your tip.... but I need to be more spesific...... lets say I got 4 cells in a row with 1 then two cells with 0 then I must start to count over again.... I only wanna count the larges number of equal cells in a row (1-1-1-1-0-1-0-) the result will be: number 1= 4 number 0= 1 Hopefully this will explain my problem better..... "Bob Phillips" wrote: =FREQUENCY(A1:A13,A1:A13) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "fiur" wrote in message ... I got a worksheet with only 2 different numbers in each cell like this: 1 1 1 0 0 1 0 0 1 1 1 My question are how do I count the largest number of equal cells... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to count the highest number of equal cells
thanx alot !!!!!!! that was exactly what I was looking for!!!!!
You're the man..... "Biff" wrote: Use a helper column. Assume your numbers are in the range A1:A11 (assuming no empty cells within the range). Enter 1 in B1. Enter this formula in B2 and copy down to B11: =IF(A2=A1,B1+1,1) Then, to get the highest number of consecutive cells: =MAX(B2:B11) If you need to break it out by specific number, eg: max for 1 and max for 0: =SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11)) =SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11)) Biff "fiur" wrote in message ... thanx alot for your tip.... but I need to be more spesific...... lets say I got 4 cells in a row with 1 then two cells with 0 then I must start to count over again.... I only wanna count the larges number of equal cells in a row (1-1-1-1-0-1-0-) the result will be: number 1= 4 number 0= 1 Hopefully this will explain my problem better..... "Bob Phillips" wrote: =FREQUENCY(A1:A13,A1:A13) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "fiur" wrote in message ... I got a worksheet with only 2 different numbers in each cell like this: 1 1 1 0 0 1 0 0 1 1 1 My question are how do I count the largest number of equal cells... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to count the highest number of equal cells
You're welcome. Thanks for the feedback!
Biff "fiur" wrote in message ... thanx alot !!!!!!! that was exactly what I was looking for!!!!! You're the man..... "Biff" wrote: Use a helper column. Assume your numbers are in the range A1:A11 (assuming no empty cells within the range). Enter 1 in B1. Enter this formula in B2 and copy down to B11: =IF(A2=A1,B1+1,1) Then, to get the highest number of consecutive cells: =MAX(B2:B11) If you need to break it out by specific number, eg: max for 1 and max for 0: =SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11)) =SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11)) Biff "fiur" wrote in message ... thanx alot for your tip.... but I need to be more spesific...... lets say I got 4 cells in a row with 1 then two cells with 0 then I must start to count over again.... I only wanna count the larges number of equal cells in a row (1-1-1-1-0-1-0-) the result will be: number 1= 4 number 0= 1 Hopefully this will explain my problem better..... "Bob Phillips" wrote: =FREQUENCY(A1:A13,A1:A13) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "fiur" wrote in message ... I got a worksheet with only 2 different numbers in each cell like this: 1 1 1 0 0 1 0 0 1 1 1 My question are how do I count the largest number of equal cells... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to count the highest number of equal cells
An array formula without helper cells is more difficult to "maintain", but it
can be done in this case... -- Regards, Luc. "Festina Lente" "Biff" wrote: You're welcome. Thanks for the feedback! Biff "fiur" wrote in message ... thanx alot !!!!!!! that was exactly what I was looking for!!!!! You're the man..... "Biff" wrote: Use a helper column. Assume your numbers are in the range A1:A11 (assuming no empty cells within the range). Enter 1 in B1. Enter this formula in B2 and copy down to B11: =IF(A2=A1,B1+1,1) Then, to get the highest number of consecutive cells: =MAX(B2:B11) If you need to break it out by specific number, eg: max for 1 and max for 0: =SUMPRODUCT(MAX((A$1:A$11=1)*B$1:B$11)) =SUMPRODUCT(MAX((A$1:A$11=0)*B$1:B$11)) Biff "fiur" wrote in message ... thanx alot for your tip.... but I need to be more spesific...... lets say I got 4 cells in a row with 1 then two cells with 0 then I must start to count over again.... I only wanna count the larges number of equal cells in a row (1-1-1-1-0-1-0-) the result will be: number 1= 4 number 0= 1 Hopefully this will explain my problem better..... "Bob Phillips" wrote: =FREQUENCY(A1:A13,A1:A13) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "fiur" wrote in message ... I got a worksheet with only 2 different numbers in each cell like this: 1 1 1 0 0 1 0 0 1 1 1 My question are how do I count the largest number of equal cells... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count cells that contain a number & are not shaded | Excel Discussion (Misc queries) | |||
count them number of cells between values -2 and 2 including 0 | Excel Discussion (Misc queries) | |||
Count number of cells of a particular month in a column of dates | Excel Discussion (Misc queries) | |||
How do I count the number of cells in a list, e.g. H6:H12, J4, J7: | Excel Worksheet Functions | |||
Count number of shaded cells | Excel Discussion (Misc queries) |