| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max number of consectutive blanks in a range
I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max number of consectutive blanks in a range
In H1 enter1
In H2 enter: =IF(G2="",H1+1,0) and copy down In another cell the answer is: =MAX(H:H) -- Gary''s Student - gsnu201001 "Steve" wrote: I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max number of consectutive blanks in a range
Perfect.
I thought it'd be simple, but actually simplier than I thought. Thanks again, Steve "Gary''s Student" wrote: In H1 enter1 In H2 enter: =IF(G2="",H1+1,0) and copy down In another cell the answer is: =MAX(H:H) -- Gary''s Student - gsnu201001 "Steve" wrote: I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max number of consectutive blanks in a range
You are welcome! You can use an array formula instead of the helper column,
but it might get really slow. -- Gary''s Student - gsnu201001 "Steve" wrote: Perfect. I thought it'd be simple, but actually simplier than I thought. Thanks again, Steve "Gary''s Student" wrote: In H1 enter1 In H2 enter: =IF(G2="",H1+1,0) and copy down In another cell the answer is: =MAX(H:H) -- Gary''s Student - gsnu201001 "Steve" wrote: I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max number of consectutive blanks in a range
This is fine.
Don't want No Slow Thanks again. "Gary''s Student" wrote: You are welcome! You can use an array formula instead of the helper column, but it might get really slow. -- Gary''s Student - gsnu201001 "Steve" wrote: Perfect. I thought it'd be simple, but actually simplier than I thought. Thanks again, Steve "Gary''s Student" wrote: In H1 enter1 In H2 enter: =IF(G2="",H1+1,0) and copy down In another cell the answer is: =MAX(H:H) -- Gary''s Student - gsnu201001 "Steve" wrote: I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max number of consectutive blanks in a range
Try this array formula** :
=MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H 700<"",ROW(H3:H700)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max number of consectutive blanks in a range
This works nicely also.
Thank you very much. Steve "T. Valko" wrote: Try this array formula** : =MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H 700<"",ROW(H3:H700)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Max number of consectutive blanks in a range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... This works nicely also. Thank you very much. Steve "T. Valko" wrote: Try this array formula** : =MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H 700<"",ROW(H3:H700)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this formula in column G =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"") In Column H, I like to count the maximum number of consecutive blanks in that entire range. Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18 to be returned. Thanks, Steve . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
| Naming Consectutive Excel Tabs | Excel Worksheet Functions | |||
| selecting non consectutive cells/rows/columns in excel 2007 | Excel Discussion (Misc queries) | |||
| countif with two sets of non consectutive ranges at the same time | Excel Worksheet Functions | |||
| copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Linear Mode
