| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maximum Number of Blank Cells between Non Blank Cells in a Range
I am trying to count the Maximum Number of Blank Cells between Non Blank
Cells in a Range. e.g. Range A1:a13 a1 - 2 a3 - blank a4 - blank a5 - 24 a6 - blank a7 - 7 a8 - 51 a9 - blank a10 - blank a11 blank a12 - blank a13 - 6 The answer is 4. Cells a9 to a12. Any help appreciated. Thanks, Mal |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Maximum Number of Blank Cells between Non Blank Cells in a Range
=COUNTBLANK(A1:A16)
"Mal" wrote: I am trying to count the Maximum Number of Blank Cells between Non Blank Cells in a Range. e.g. Range A1:a13 a1 - 2 a3 - blank a4 - blank a5 - 24 a6 - blank a7 - 7 a8 - 51 a9 - blank a10 - blank a11 blank a12 - blank a13 - 6 The answer is 4. Cells a9 to a12. Any help appreciated. Thanks, Mal |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Maximum Number of Blank Cells between Non Blank Cells in a Range
This ARRAY FORMULA, committed with Ctrl+Shift+Enter (instead of just Enter)
returns the count of the maximum consecutive blanks (or empty strings): =MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13),ROW( A1:A12)),IF((A1:A12<"")*(A1:A12<A2:A13),ROW(A1:A 12))))+(COUNTBLANK(A1:A13)0) Since text wrap may impact the display, there are no spaces in that formula. Also, here is that same formula, broken into sections: =MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13), ROW(A1:A12)),IF((A1:A12<"")*(A1:A12<A2:A13), ROW(A1:A12))))+(COUNTBLANK(A1:A13)0) Using your posted data, that formula returns: 4 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Mal" wrote in message ... I am trying to count the Maximum Number of Blank Cells between Non Blank Cells in a Range. e.g. Range A1:a13 a1 - 2 a3 - blank a4 - blank a5 - 24 a6 - blank a7 - 7 a8 - 51 a9 - blank a10 - blank a11 blank a12 - blank a13 - 6 The answer is 4. Cells a9 to a12. Any help appreciated. Thanks, Mal |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Maximum Number of Blank Cells between Non Blank Cells in a Range
This simplified version also works (array entered):
Note that both empty cells and cells that may contain formula blanks are considered "blanks". =MAX(FREQUENCY(IF(rng="",ROW(rng)),IF(rng<"",ROW( rng)))) -- Biff Microsoft Excel MVP "Ron Coderre" wrote in message ... This ARRAY FORMULA, committed with Ctrl+Shift+Enter (instead of just Enter) returns the count of the maximum consecutive blanks (or empty strings): =MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13),ROW( A1:A12)),IF((A1:A12<"")*(A1:A12<A2:A13),ROW(A1:A 12))))+(COUNTBLANK(A1:A13)0) Since text wrap may impact the display, there are no spaces in that formula. Also, here is that same formula, broken into sections: =MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13), ROW(A1:A12)),IF((A1:A12<"")*(A1:A12<A2:A13), ROW(A1:A12))))+(COUNTBLANK(A1:A13)0) Using your posted data, that formula returns: 4 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Mal" wrote in message ... I am trying to count the Maximum Number of Blank Cells between Non Blank Cells in a Range. e.g. Range A1:a13 a1 - 2 a3 - blank a4 - blank a5 - 24 a6 - blank a7 - 7 a8 - 51 a9 - blank a10 - blank a11 blank a12 - blank a13 - 6 The answer is 4. Cells a9 to a12. Any help appreciated. Thanks, Mal |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Maximum Number of Blank Cells between Non Blank Cells in a Range
Thanks for your help people.
Problem solved. Regards, Mal "T. Valko" wrote in message ... This simplified version also works (array entered): Note that both empty cells and cells that may contain formula blanks are considered "blanks". =MAX(FREQUENCY(IF(rng="",ROW(rng)),IF(rng<"",ROW( rng)))) -- Biff Microsoft Excel MVP "Ron Coderre" wrote in message ... This ARRAY FORMULA, committed with Ctrl+Shift+Enter (instead of just Enter) returns the count of the maximum consecutive blanks (or empty strings): =MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13),ROW( A1:A12)),IF((A1:A12<"")*(A1:A12<A2:A13),ROW(A1:A 12))))+(COUNTBLANK(A1:A13)0) Since text wrap may impact the display, there are no spaces in that formula. Also, here is that same formula, broken into sections: =MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13), ROW(A1:A12)),IF((A1:A12<"")*(A1:A12<A2:A13), ROW(A1:A12))))+(COUNTBLANK(A1:A13)0) Using your posted data, that formula returns: 4 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Mal" wrote in message ... I am trying to count the Maximum Number of Blank Cells between Non Blank Cells in a Range. e.g. Range A1:a13 a1 - 2 a3 - blank a4 - blank a5 - 24 a6 - blank a7 - 7 a8 - 51 a9 - blank a10 - blank a11 blank a12 - blank a13 - 6 The answer is 4. Cells a9 to a12. Any help appreciated. Thanks, Mal |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Maximum Number of Blank Cells between Non Blank Cells in a Range
Hello,
If only real blank cells qualify, array-enter: =MAX(FREQUENCY(ISBLANK(rng)*ROW(rng),NOT(ISBLANK(r ng))*ROW(rng))) And if you *really* meant "between non blank cells" then define, for example, a name rng2: =INDEX(rng,MATCH(FALSE,ISBLANK(rng),)):INDEX(rng,L OOKUP(2,1/ (FALSE=ISBLANK(rng)),ROW(rng))) and apply the MAX formula to rng2. Regards, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fill Blank cells in a range | Excel Discussion (Misc queries) | |||
| Hide row if all cells in another range are blank | Excel Discussion (Misc queries) | |||
| clear range of cells if another becomes blank | Excel Worksheet Functions | |||
| Range object without blank cells | Excel Discussion (Misc queries) | |||
| How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) | |||
Linear Mode
