calculating subtotals for sequential blank cells in 1 column

if i had a column of data and i need to group/subtotal any cells containing 
zeros or isolated zeros, wat formula do i put in 
eg. 

0   )
1   )
0   )
0   ) subtotal = 4 (so i know it was a group of four cells)
4   
5   
1
6
5
0 )
1 )
0 ) subtotal = 3
6
7
8
0 ) subtotal = 1

basically the data shows steps and rests, and we need to know how long the 
rest periods are. single steps surrounded by rest (0) need to be counted as 
rest intervals. 


0
Utf
12/9/2009 11:30:27 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
771 Views

Similar Articles

[PageSpeed] 55

Here is one way. Assuming your data starts in A1...

In B1 enter    =--(A1=0)
In B2 enter    =IF(A2=0,B1+1,IF(AND(A1=0,A3=0),B1+1,0))
Copy B2 down through all rows of data.

In C1 enter    =IF(AND(B2=0,B1<>0),"subtotal = "&B1,"")
Copy C1 down through all rows of data.
Add a non-zero dummy number (99999) after the last data cell in column A (no 
formulas in B or C next to the dummy number; it just ensures that the last 
data cell in A gets handled correctly by the formulas in B & C).
Hide column B if desired.

Hope this helps,

Hutch

"Katie" wrote:

> if i had a column of data and i need to group/subtotal any cells containing 
> zeros or isolated zeros, wat formula do i put in 
> eg. 
> 
> 0   )
> 1   )
> 0   )
> 0   ) subtotal = 4 (so i know it was a group of four cells)
> 4   
> 5   
> 1
> 6
> 5
> 0 )
> 1 )
> 0 ) subtotal = 3
> 6
> 7
> 8
> 0 ) subtotal = 1
> 
> basically the data shows steps and rests, and we need to know how long the 
> rest periods are. single steps surrounded by rest (0) need to be counted as 
> rest intervals. 
> 
> 
0
Utf
12/10/2009 3:16:08 AM
Reply:

Similar Artilces:

Free rotate cell content?
I need to know how to free rotate the content of a cell in Excel. I also need to know how to free rotate an imbed in Excel. Contents of cells can be rotated using Format/Cell/Alignment. It's not free-rotation, but is limited to plus or minus 90 degrees. You can get free rotation if you copy a cell as a picture (hold the shift key down as you select Edit/Copy Picture) and paste as a picture into the worksheet (Edit/Paste Picture). You can then use the Free Rotate tool on the Drawing Toolbar to rotate the picture as desired. In article <72952FA1-EF94-4192-AA34-AFA97FEB4FFD@mi...

Excel 2007 opens as a blank/gray screen
I have MS Windows XP, Prof Version 2002, sp3 and my problem is whenever I double click one of my Excel 2007 files, Excel opens as a blank/gray screen. I open many excel files each day and this is really frustrating. I've been looking around and searching for an answer online and I've found and tried two possible solutions: I clicked the Excel 2007 office button, Excel options, Advanced, and under General the box for 'ignore other aps' was unchecked - so that didn't work (I checked it and unchecked it again and no help). I also tried this: Start Run e...

Excel formula calculating between #'s
I am working on a bonus chart and this is what I am trying to do: If an employee keeps his register balances between -$10.00 thr +$10.00, they recieve 100 points. If they are out of that range the get 0 points. I have tried playing with "IF" formulas until I am blu in the face. Please help Thanks Mike Manic -- Message posted from http://www.ExcelForum.com Hi Mike try something like =IF(AND(A1>=-10,A1<=10),100,0) -- Regards Frank Kabel Frankfurt, Germany "manickmj >" <<manickmj.15ftz3@excelforum-nospam.com> schrieb im Newsbeitrag news:manickmj.1...

Export to specific cells in Excel template from ACCESS
I am planning to export my access table to an excel template R19 to R50 and C1: C8. Is there a way to do this using VBA? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 I am no expert but dealing with current problem very similar and this link to article written by Doug Steele may be just the answer you are looking for. http://www.accessmvp.com/DJSteele/SmartAccess.html July 2005: Excelling automatically. This will download zip file explain sample db that shows code for multiple ways to intera...

Chart title = cell contects
How do I set my chart title to the contents of a cell, and then what ever the cell contents are, the chart title is changed to automatically? Dean -- dkso@ntlworld.com http://www.dkso.co.uk/ http://homepage.ntlworld.com/dkso Hi, Here is an explanation, http://www.andypope.info/tips/tip001.htm Cheers Andy Dkso wrote: > How do I set my chart title to the contents of a cell, and then what ever > the cell contents are, the chart title is changed to automatically? > > Dean -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Any, Thanks, just what I was after ho...

Tools/Options/Charts-Active cells is dimmed. Want to select leave
I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in chart, but still showed zeros. When I went to Tools/Options/Charts, the Active Cells area was dimmed and I could not select "leave gaps". Any suggestions? Thanks, Hi, Was the chart selected when you did Tools>Options ? Cheers Andy teds wrote: > I tried using #N/A in equation IF(B7=0,#N/A,B7) as to not show zeros in > chart, but still showed zeros. When I went to Tools/Options/Charts, the > Active Cells area was dimmed and I could not select "leave gaps". Any > suggestions? &...

Automatically update cells in worksheets
Excel newbie here hoping someone can point me in the right direction... Have a workbook that contains 3 worksheets... ws1: Is a work detail list I enter the date and unique ID no. and Company Name. ws2: Contains a list of the first 30 Companies I have to visit each month, and contains all the Company details such as address, tel no, ID no., ws3: Contains a full list of all Companies within the area I'm working that month, including the initial 30 dealers. Now what I'm trying to do is when I enter the Company Name in ws1 it will automatically update Format conditions in ws2 and 3.....

What is the property and syntax to format a cell as text
With ActiveCell .Offset(, 3).NumberFormat = "Text" this fails - runtime error 1004, unable to set NumberFormat .Offset(, 3).NumberFormat = "General" works OK What is the property/syntax to format a cell as text? Eggle, try, .Offset(, 3).NumberFormat = "@" -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Eggle" <Eggle@discussions.microsoft.com> wrote in message news:761F249B-...

Office 10 1.5 and Panther Fax
I have been successfully using OS X.3's new Fax facility on all applications - it lets me pull fax numbers from OS X Address Book in every program except Office! The "To:" field normally automatically fills in the intended recipient's name from my first few key strokes. However, in Office, I have to enter everything from memory. Even if I bring up the People list, they cannot be selected. Anyone have any idea when MS is going to fix this? Or, is it just my installation? I am using a PowerBook G4/1Mhz/512Mb RAM/60Gb HD/Panther/Office X 1.5. TIA, Bette In article <9e82a...

show active cell
Hi I want to show the contents i the active cell in another cell, fo example cell A1. Also when i change active cell. dalla -- Message posted from http://www.ExcelForum.com Hi, Not sure what you mean exactly. But if you want B1 to always show wha A1 has, in B1 type ... =A1 Is that what you're looking for -- Message posted from http://www.ExcelForum.com One way: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_SelectionChange( _ ByVal Target As Excel.Range) Range("A1").Value = ...

combining data into subtotal report
I need to manually enter data under various categories. (i.e. column A will be Job # and the other columns will contain the counts of various supplies), creating a very large running total of jobs and total supplies. It is important to realize that I may enter the same job # with new data more than one time. Now, I understand how to create subtotals of columns, but I was wondering if, once I have sorted data by job #, there is a way to create a fast and easy report that indicates ONLY the column headings and each job's subtotal. I do not want to see the 25 different entries that it ...

Is there a way to split a sheet so columns can be different widths?
I have a bunch of worksheets that have a top part and a bottom part. The top part contains settings for various parmeters. The bottom part is a table showing calculations based on those parameters. The problem is that the parameters often need different column widths than the table. Is there some way to split a sheet into a top part and a bottom part were I can adjust the column widths independently? Column widths and row heights are properties of the entire column and row and cannot be changed. I hesitate to mention "merged cells" due to the problems they can cause. ...

Subtotals by Page
I have a report that has an [hours] field. I have a totals field in the report footer that calculates the total hours for the whole report (about 1200 records), but I also need a subtotal for each page. The grand total in the report footer works fine, but any calculation I put in the page footer returns an error. Any ideas what I might be doing wrong? Terry On Sat, 28 Jul 2007 13:42:05 -0400, TDS wrote: > I have a report that has an [hours] field. I have a totals field in the > report footer that calculates the total hours for the whole report (about > 1200 records), but I...

Formulas to highlight cell if condition is met
i wonder if there is any way i can highlight a cell if a condition is met? eg i want to highlight the cell if the num in the cell is greater than 1. do i hv tio use macros? -- hmmm ------------------------------------------------------------------------ hmmm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25836 View this thread: http://www.excelforum.com/showthread.php?threadid=481784 No you can use conditional formatting Select the cell Menu Format>Conditional Formatting Set to greater than Set value to 1 Click the Format button Select the Pattern Tab ...

unhide rows when cell is clicked
Hi everyone, Is it possible to unhide a selection of rows when you click on a cell. For example, if I had a cell that said "Fruit", and I clicked it, could it unhide 5 rows below that contained specifc types of fruit (ie apple, pear, orange etc). I am in the very beginning stages of understanding excel so any help would be greatly appreciated (but could it also be as straightforward as possible!! thank you!!!) Thanks! lauren You would need to create an event macro to do what you're asking. In the interest of keeping things more simple, perhaps the AutoFilter option might ...

Excel Data Not Visible in Cells 2007
I have two users in my office that use 2007 - myself and my manager. This morning something strange happened to a spreadsheet he was working on. There was data in the first 200 lines of the spreadsheet. somewhere around line 35 and up to line 60 the data - dissapeared. You couldn't see the text in the cells. BUT if you clicked in the cells you could see the info in the formula bar. He clicked save, and then it showed all the information again. This has happend several times over several spreadsheets. Can anyone help explain this?? Thanks ...

Form goes blank when no records 12-15-09
Messaggio multiparte in formato MIME. ------=_NextPart_000_009F_01CA7D8E.88CB66E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a form that goes blank when a user deletes all records. Is there a way to have vba to add a record when the form is open. This=20 would keep the form from going blank. ------=_NextPart_000_009F_01CA7D8E.88CB66E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">...

Restoring the light gray cell borders...
For some reason I have not been able to determine, The light gray border that surrounds each cell in a new worksheet turns white. SO , it looks like the cell has "disappeared." Of course, this is just a visual issue, since the cell still exists and works fine. My question is, how can I restore the border. I have found that it is possible to reestablish a facsimile of the original borders using the Format/Cell/Borders menu options and setting color to the lightest gray on the palette but this seems like a crude method. There must be a more direct method. What is it? John Wirt J...

possible to check a cell for both even number and positive number.....
I have found a workable formula to check for even numbers but I am not being sucessful in having it also check for positive numbers..... I am using the following data verification statment to check for even numbers and it works fine..... =MOD(G17,2)=0 problem is that the number they are supposed to enter has to be a positive number because negative numbers are invalid data in this application. Any help would be appreciated. Hi Try this one. =IF(AND(MOD(G17,2)=0,G17>0),"True","") HTH John "M G Henry" <mr_mikehenry007@hotmail.com> wrote in message new...

cell input
Hellow all, I am really new with excel. I think this is the third or fourth time that i really opened excel. But now i have to work with it and stumbled accross my first problem. I have this column where you can put data. What i want to accomplish is that you have a choice. So the only information you can put in this cell is something like a pulldown. Do i make any sense? greetz, walter +-------------------------------------------------------------------+ |Filename: pulldown.gif | |Download: http://www.excelforum.com/attachment.php?postid=3679...

Subtotals #5
I have one machine at my company that cannot apply a third subtotal when everyone else can. (We do uncheck the 'replace current subtotals' setting. She just got her machine so I am assuming that the problem is just a setting. Does anyone know what setting this may be or have a suggestion as to what the problem may be? Is she using Excel 2003? If so, in the following thread: http://groups.google.com/groups?&threadm=f60830af.0408061156.666b5f74%40posting.google.com the poster reported that changing the registry solved the problem on his computer. You could make a backup co...

Need help in Dates Calculations.
Hello every Body. I have a DB in which i want that it will show me the report by taking a Parameter through keyboard and the Parameter value should be Date. It will take the date and filter all the records of the concern date. how would be it possible? Can any one help please...? the second thing is that when i give the date of birth in the DOB Field and Current Date in the Present Date Field it whould display the Difference in years between the two dates. But how.? urgent help needed please. Regards, Khaksar. See: http://allenbrowne.com/func-08.html The article explains some...

CRM 1.2 and Exchange 2003 on Same Server
I need to install CRM 1.2 on a SBS 03 server. In the past I have had issues running CRM and OWA on the same machine, because CRM takes the default site. This caused problems for me with OWA. If any one has any input on this it will be greatly appreciated -- Thank you in advance Either add a second IP address to the server and use that for the CRM site. Or enable the use of Host Headers, and use a unique host header for each site. Then create a DNS alias for each Host header, Use that to Call up CRM or OWA. -- Paul Papanek Stork MBA, CTT+, MCT, MCSE+I, MCSA, MCAD, MCDBA Solutient of Oh...

how do i exchange 2 text boxes from pg 1 to pg 2 on a brochure
I set up a brochure in publisher and i need to put a filled out text box from page 1 to page 2 and put the filled out text box on page 2 into the spot on page 1..any way that I can easily swith those spots wthout having to do it all over again? Drag the text boxes to the scratch area and then drag them to the page wanted. -- Don ------ Vancouver, USA "kem1014" <kem1014@discussions.microsoft.com> wrote in message news:F05F427D-0698-499E-B297-921A765E7D33@microsoft.com... >I set up a brochure in publisher and i need to put a filled out text box >from > page 1...

Row and columns
In excel my rows are numbered 1 through XXX and my columns are also numbered 1 through XXX. On my other computers using the same current version of Excel the columns are identified using Alpha A,B,C,D etc. Anyone know how to change the columns to Alpha v.s. numbers? Joe, tools, optins, general, and uncheck R1C1 reference style -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Joe" <anonymous@discussions.mic...