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$700)>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
0
Utf
1/28/2010 4:21:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
1023 Views

Similar Articles

[PageSpeed] 46

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$700)>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
0
Utf
1/28/2010 5:28:01 PM
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$700)>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
0
Utf
1/28/2010 5:52:02 PM
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$700)>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
0
Utf
1/28/2010 6:09:02 PM
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$700)>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
0
Utf
1/28/2010 7:18:01 PM
Try this array formula** :

=MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H700<>"",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" <Steve@discussions.microsoft.com> wrote in message 
news:134794AF-B3CF-4AEB-8A7F-643EF0D5E316@microsoft.com...
>I have this formula in column G
> =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$700)>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 


0
T
1/28/2010 7:20:39 PM
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:H700<>"",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" <Steve@discussions.microsoft.com> wrote in message 
> news:134794AF-B3CF-4AEB-8A7F-643EF0D5E316@microsoft.com...
> >I have this formula in column G
> > =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$700)>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 
> 
> 
> .
> 
0
Utf
1/28/2010 8:57:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Steve" <Steve@discussions.microsoft.com> wrote in message 
news:C7C20D78-4A99-4CB2-9EB1-E2CFF00725E9@microsoft.com...
> 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:H700<>"",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" <Steve@discussions.microsoft.com> wrote in message
>> news:134794AF-B3CF-4AEB-8A7F-643EF0D5E316@microsoft.com...
>> >I have this formula in column G
>> > =IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$700)>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
>>
>>
>> .
>> 


0
T
1/28/2010 9:47:52 PM
Reply:

Similar Artilces:

Pub 2000: insert Unicode by number
I wish to insert a couple of Unicode characters which do not appear i Publisher 2000's symbol list. Is this possible -- Andrew Bryan ----------------------------------------------------------------------- Andrew Bryant's Profile: http://www.officehelp.in/member.php?userid=511 View this thread: http://www.officehelp.in/showthread.php?t=126125 Posted from - http://www.officehelp.i Andrew Bryant wrote: > I wish to insert a couple of Unicode characters which do not appear in > Publisher 2000's symbol list. Is this possible? Try (with NumLock on) Alt+(Uxxxx), where xxxx is...

Max Customers Accounts in CRM 1.2
One of our client who is evaluating CRM asked for number of Customer Accounts Limit of MS CRM 1.2. We know that MS is targeting CRM for small and Medium size orgainzation but can it support customers of an orgainzation to Millions. Or Can some one tell the max limit of Customer Accounts. ? Regards, JZ There is no defined maximum.. The key is sizing the SQL server appropriately. Especialyl with CRM not supporting clustered SQL servers. I know there are companies out there that have implemented (or are in the process of implementing) installs for > 1,000 users. Matt Parks MVP - Micr...

auto number a word document each time it is opened
Hi there, I would like to know how to have a number update automatically each time the document is opened. I have tried all the avenues I know and still have had no luck. I am using word 2003 and have no experience with macros, however I am keen to learn. I am struggling to get the below advice to previous users " http://www.word.mvps.org/FAQs/MacrosVBA/NumberDocs.htm. " to work for me. It keeps coming up with error messages after I have created the macro, the main one being "Compile Error: Expected Function or variable" for the "Order =" and then...

numbers in a cell change
When I type numeric numbers in a cell they change to a type of formula when I exit the cell. How do I get them to stay numeric only? -- GeorgeA I just can't picture what you're describing. Could you post some examples? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "GeorgeA" <GeorgeA@discussions.microsoft.com> wrote in message news:...

Excel formula divide list of #s by N to get a number above 40
I have a list of numbers. I need to divide each of those numbers by the same number ("N") that will give me a resulting list in which all of the numbers are at least 40 or above. Is there a formula that will find such a number? Hi =MAX(40,A1/N) Arvi Laanemets "JudyK" <JudyK@discussions.microsoft.com> wrote in message news:3FB1C4C9-745E-4CAD-A20D-0AC346CE9365@microsoft.com... > I have a list of numbers. I need to divide each of those numbers by the same > number ("N") that will give me a resulting list in which all of the numbers > are at l...

Export number without decimals
hI, When I export my data from a query in csv format, one of the fields which has 13 digit numbers diplays decimal places when infact the data in the database does not have any decimals. How can I force this field in my query to format with zero decimals so it exports correctly. Eg. currently doing 1310010010011.00 want to show 1310010010011 Bruce Bruce It might be a bit of a kludge, but you could convert the number to text (CStr()) in a query before exporting the query. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/...

Formatting Page Numbers
I am formatting a manual that supposed to have page numbers starting 0.0, 0.1, ... then 1.1, 1.2, 1.3, etc. with an accompanying TOC. I am perfectly willing to put the page numbers in manually to make sure that they are done accurately, but I cannot figure out how to turn off the automatic page numbers or format them the way I need to. There must be something I can do with section breaks, but the revision date is alternating vertically on the side of the pages because it's a manual. Thoughts on how I can make the page numbers work out? What dictates the change from the ...

"Reply to All" returns blank email
I'm running Outlook 2002 client with all the SP's applied on Exchange 5.5, and the glitch is this: After receiving an email as a recipient of a distribution list, I "Reply to All", and after editing and sending, the email is received with a blank message body and a blank subject line. MSKB #290032 indicates that the latest Office SP should correct it, but it doesn't. A quick and dirty solution was to create a "New" email message and cut and paste all the info. Any ideas as to what would cause this behavior? ...

Adding a version number to a document
Is it possible to add a version number to a workbook on saving and closing Hi Tigsboy Yes it is possible. You can manually change the name of the document, e.g. from Draftv1.xls to Draftv1.1.xls. Or else, you can use a macro to increment your version number every time you save it -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "Tigsboy" wrote: > Is it possible to add a version number to a workbook on saving and closing Good afternoon Tigsboy You will need a macro to do this for you, but just to put you on th right track: Private Sub Workbo...

Offset with Blank Rows?
At the top of the column, I want to sum the last ten entries ignoring blank cells. Such as: $3,034.70 $255.10 $285.50 $339.80 $335.70 $302.70 $304.00 $268.40 $276.90 $264.90 $401.70 Every day there is a new entry, and at the top of the column I just want the sum of the last ten entries ignoring the blanks. Do I use the offset function for this? hi Blake, array formula to validate with ctrl + shift + enter =SUM(INDIRECT("A2:A"&MAX(IF(A2:A65535<>"",ROW(A2:A65535))))) -- isabelle Le 2011-05-24 12:55, Blake a �crit : > At the top of the column, I want...

Max and min
Hi! I would like a formula to anticipate the expected maximum and minimum of votes for each political group in the elections and that the variables are necessary to know Thanks in advance. an ...

maximum number of recipients
I am using Microsofts Outlook 2007. What is the maximum number of recipients that can be added to send one message to? I get an error code 452 when I want to send one message to a few recipients simultaniously. Which is an error code and limitation set by your ISP and is not coming from Outlook. You'll have to contact your ISP to find out which limit applies to your subscription. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real ...

How can I display negative numbers in a floating bar graph?
I have two categories where the minimum value is a negative number. When Excel graphs these two categories, it starts at zero, rather than the negative number. I can't figure out how to get Excel to display the numbers accurately. Keeping in mind that a floating chart is a stacked chart, follow the procedures on this page: http://peltiertech.com/Excel/Charts/StackedColumnsAboveAndBelow.html In short, the bars cannot cross the axis, so you need two series, one for the positive part of a bar, and another for the negative part. - Jon ------- Jon Peltier, Microsoft Excel MVP Pelti...

find header then replace header with number of entries below heade
I have a data set that contains over 66,000 lines. I would like to search for each 'nan nan' then replace it with the number of entries that follow until the next 'nan nan'. Ex. the first 'nan nan' to be replaced with '7', second with '17'. I would also like to enter a '1' in the cell to the right of each replaced value. Thanks in advance. Kevin nan nan -89.34548 29.000135 -89.34636 28.998375 -89.346654 28.997788 -89.347534 28.997788 -89.347827 28.997495 -89.346654 28.996321 -89.341373 29.000135 nan nan -89.424686 28.9232...

2 data ranges shown on 1 line with 2 axis
Is it possible to have 2 data ranges plotted on one line against 2 value axis?? The X axis will be a dateline. I need to be able to look at a point on the line e.g., 3/07/05 and see a value on the Z axis and a different value on the Y Axis... but on 1 line not 2. I didnt think it was possible, but I have an engineer who believes this is possible. Help please ! cheers, Nadia Excel does not do 3D charts well. Maybe someone else can point you to ways to kluge this. "Nadia" <Nadia@discussions.microsoft.com> wrote in message news:C4186078-FEDE-45D4-9849-C7B25804D945@micro...

Does Smartlist have max # of records it looks at?
I created a report through SmartList builder and when I run it, the information stops at 12/7/09. It does not show the rest of the year regardless of what date criteria I use. I then created a similar SQL query and made a new SmartList from that query. Again the information stops at 12/7/09. Is there a maximum number of records that SmartList will look at? Or any ideas on why this is happening? This is my SQL query: select a.ordocnum, a. crdtamnt, a.debitamt, a.trxdate, a.actindx, a.ormstrid, a.ormstrnm, a.dscriptn, b.mnacsgmt, c.userdef1, c.userdef2 from gl20000 a lef...

Hindi Number Format
Is ther an equivalent in Access for a for a number format available inExcel .NumberFormat = "[$-2000000]#0,000.00"so that I can have Hindi numerals in a reports.when I use Numeral shapes to context or system or National it is notgiving me the desired result.How I can apply this in a Report so that the field will be formatedusing the above way?Thanks...

Blank networth reports
Hi, Whenever I run the net worth reports, I get the hourglass for a second or two, then loading but it never renders. Other reports (Transactions, loans, cap gains, etc.) run fine. I converted from Quicken. In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >Hi, Whenever I run the net worth reports, I get the >hourglass for a second or two, then loading but it never >renders. Other reports (Transactions, loans, cap gains, >etc.) run fine. Perhaps customize the report to a different time frame, and then reset the report? Tried all dates. >...

Formula For Sorting Part Numbers
I have Excel 2000. Sheet1 of my workbook has: * Part numbers in column D * Primary bin location in column F * Secondary bin location(s), if any, in columns I, J, K, L * Each part number and corresponding bin location(s) are listed on the same row and there are no duplicate part numbers I need help with a formula (to be inserted in Sheet2) that does the following: * List all the part numbers and bin locations (primary + secondary) if there is an "X" in column AB of any part number listed in Sheet1 * All the information for a part number should be on a single row * T...

Total number?
hi, I want to display a total number for all the number in series, how to do that? please advice. thanks. Hi What is 'number in serias'? <someone@js.com> wrote in message news:%23jz07DtkKHA.2132@TK2MSFTNGP05.phx.gbl... > hi, I want to display a total number for all the number in series, how to > do > that? please advice. thanks. > > Sum() all number but only the group/category. How to do that? "Uri Dimant" <urid@iscar.co.il> wrote in message news:OZlsMQ2kKHA.3792@TK2MSFTNGP02.phx.gbl... > Hi > What is 'num...

Add hyphens to a number
In one column, each cell has a 16 digit number. I need to put hyphens between 4 different numbers in each cell. Each cell/number will have the hyphen in the same place. I was successful doing this once I believe through Format\Cells\Number\Custom but I did not write down the steps how to do it. Any clues? Thanks texasphil -- texasphil ------------------------------------------------------------------------ texasphil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32615 View this thread: http://www.excelforum.com/showthread.php?threadid=541305 You didn&#...

MAX
Hi, I am looking for a query that will return all the entries where a certain field has its max value. For example: I am interested in all the values where version is at its MAX for a certain id so if my table is: id idea status version 1 Apple open 1 1 Banana close 2 2 Chair close 1 3 Table open 3 3 Flower open 4 4 Keyboard open 1 I would expect to get: 1 Banana close 2 2 Chair close 1 3 Flower open 4 4 Keyboard open 1 Any idea? Thanks, Dan Create a query that selects the id and max(version) Create a second query that joins the table...

ability to configure wait object condition with max wait time capp
configuring wait for object condition results in a wf instance that may wait indefinitely long. It would be good if we could cap the wait period at a specified perod of time or object status change. e.g. wait for case.priority = high (abort wf instance after 6 months of waiting) wait for case.priority = high (abort wf instance after case.status = resolved, cancelled) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do n...

Transfer Out Serial Number Issue
I'm working on setting up RMS for a store. We have many items with serial numbers that we wish to be able to track. We sell many of the items retail but also use them internally to build assemblies that we sell. I have the database set up with the items and have serial numbers entered for the items. We are trying to use Inventory Transfer Out to take the serial items out of inventory for our assemblies and then Transfer In the completed assemblies. I am seeing a problem with the serial numbers when issuing and committing the items. Transfer Out doen't appear to check the...

average a row of numbers?
I am trying to get the average of a row of numbers with various numbers of columns. I have 50 rows of numbers and am looking for the formula to average all of the rows, showing the average of each row. I know how to do them individually, but very time consuming. Any help would be appeciated. Insert a column and enter =AVERAGE(B1:IV1) Copy down 50 rows. Gord Dibben MS Excel MVP On Mon, 6 Aug 2007 16:56:01 -0700, wannabe68 <wannabe68@discussions.microsoft.com> wrote: >I am trying to get the average of a row of numbers with various numbers of >columns. I have 50 rows ...