Today I had a couple of questions that stumped me and relied on my best friends, Microsoft Office Support Community and Google to provide the answers. Thanks to these great resources!
Question 1: I was working on a Word document for my boss and can’t read the tiny comments.
Solution: Somehow the styles for the balloon got changed. To change it, go to your styles (click the diagonal arrow in the styles section) and click the Manage Styles icon in the bottom of the Styles window. Sort Alphabetical and find Balloon Text and click Modify to change to a larger font such as 8 or 10 and click OK. This one was on 1 pt, making it impossible to read. For complete instructions with screen prints, see this link.
Question 2: I need to count a series of patterns in an Excel column containing only 0s and 1s. I’m looking for patterns in a column with many rows where there is a 0 and then a 1 in the next row below it. It doesn’t matter if it is followed by several 1s. I just need to count all of the 0′s followed by at least one 1.
I submitted the question to Microsoft Online Community and got 3 answers within an hour.
Solution 1:
Assuming the data is in column A, this is the formula:
=SUMPRODUCT(($A$1:$A$998=0)*($A$2:$A$999=1))
Solution 2:
I like Jeeped’s solution (solution 1) - just remember that if you use it, the formula itself cannot be in one of the cells in the addresses in it. In other words if you wanted the total at the end of the group you’ve shown in your question, and assuming that goes from row 2 to 17, your formula (in cell on row 18 in same column) would look something like:
=SUMPRODUCT(($A$2:$A$16=0)*($A$3:$A$17=1))
Just as an option, I’ll give you a User Defined Function (UDF) to do the same thing. A UDF is a special type of macro that can be used in a worksheet formula just like built-in Excel functions such as SUMPRODUCT().
I called this one CountPairs. And you would use it on a worksheet like this:
=CountPairs(A2:A17)
assuming your list of 1s and 0s is in column A from row 2 through 17.
Here is a web page that tells how to put the code into a regular code module for use:
http://www.contextures.com/xlvba01.html#Regular
Here is the code for the UDF:
Function CountPairs(listRange As Range) As Long
‘place formula such as
‘ = CountPairs(A2:A16) to get count
‘of zeros followed by a 1 in the group
Dim rowPtr As Long
Dim tempCount As Long
For rowPtr = listRange.Cells(1, 1).Row To _
(listRange(listRange.Cells.Rows.Count, 1).Row – 1)
If Cells(rowPtr, 1) = 0 _
And Cells(rowPtr + 1, 1) = 1 Then
tempCount = tempCount + 1
End If
Next
CountPairs = tempCount
End Function
