Ok, where were we?
Oh, consecutive 0s.
For the min consecutive 0s:
=MIN(FREQUENCY(IF(M4:M13=0,ROW(M4:M13)),IF(M4:M13<>0,ROW(M4:M13))))
For the max consecutive 0s:
=MAX(FREQUENCY(IF(M4:M13=0,ROW(M4:M13)),IF(M4:M13<>0,ROW(M4:M13))))
Note that empty cells evaluate as 0 and could be counted! Both formulas are
array formulas. Array formulas need to be entered using the key combination
of CTRL,SHIFT,ENTER (not just ENTER)
Formula explanation....
=COUNTIF(G30:INDEX(G3:G30,LOOKUP(2,1/(G3:G30=1),ROW(G3:G30)-MIN(ROW(G3:G30))+1)),0)
Count the number of 0s from the last cell in the range up to the first cell
that contains a 1.
Ok, the first thing to realize is that Excel is written to work from left to
right and from top to bottom. So, another way to state what you wanted to do
is:
Find the *last instance* of 1 in the range and count the number of 0s from
that point to the end of the range. Keeping within the parameters of Excel
working from top to bottom that's what we're going to do.
Let's use this small sample to demonstrate this:
G3 = 0
G4 = 1
G5 = 1
G6 = 0
G7 = 0
Here's one of the formulas we could use:
=COUNTIF(G7:INDEX(G3:G7,LOOKUP(2,1/(G3:G7=1),ROW(G3:G7)-MIN(ROW(G3:G7))+1)),0)
When all is said and done this is what the above formula will evaluate to:
=COUNTIF(G5:G7,0)
That's pretty straightforward but to get to that point we have to use all
this other stuff:
G7:INDEX(G3:G7,LOOKUP(2,1/(G3:G7=1),ROW(G3:G7)-MIN(ROW(G3:G7))+1))
Ok, G7 is the end of the range so we can figure out what this much of that
stuff means:
G7:
Now we have to find the beginning of the range we're interested in which
starts at the location of the last instance of 1. To find the last instance
of 1 we use this:
LOOKUP(2,1/(G3:G7=1),ROW(G3:G7)-MIN(ROW(G3:G7))+1)
We want to lookup (find) the value of 2 (the lookup_value) in the
lookup_vector 1/(G3:G7=1) and return the corresponding value from the
result_vector ROW(G3:G7)-MIN(ROW(G3:G7))+1.
The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value. We're going to "leverage"
that behavior to find the last instance of 1 in the range.
This portion will return an array of either TRUE (T) or FALSE (F):
(G3:G7=1)
G3 = 1 = F
G4 = 1 = T
G5 = 1 = T
G6 = 1 = F
G7 = 1 = F
Remember that our lookup_value is 2. We're trying to find the number 2 in
the lookup_vector but so far all we have is a bunch of TRUE and FALSE. So we
need to convert those T's and F's to numbers. Here's how we do that:
1/(G3:G7=1)
We divide each result of (G3:G7=1) by 1:
1/(G3=1) = 1/(FALSE) = #DIV/0!
1/(G4=1) = 1/(TRUE) = 1
1/(G5=1) = 1/(TRUE) = 1
1/(G6=1) = 1/(FALSE) = #DIV/0!
1/(G7=1) = 1/(FALSE) = #DIV/0!
OK, so now we have some numbers in the lookup_vector. At this point the
LOOKUP function looks like this:
LOOKUP(2,{#DIV/0!;1;1;#DIV/0!;#DIV/0!},ROW(G3:G7)-MIN(ROW(G3:G7))+1)
Now we can take a look at the result_vector:
ROW(G3:G7)-MIN(ROW(G3:G7))+1)
Remember our goal here is to find the last instance of 1 in the range G3:G7.
Looking at our data we can see that the last instance of 1 is in cell G5. To
find it we use the INDEX function to "hold" the values of the range and
we're using the LOOKUP function to tell INDEX where that value is located.
When you index a range of cells the values of the cells are indexed in
sequential positions. In our sample these values are indexed like this:
G3 = position 1
G4 = position 2
G5 = position 3
G6 = position 4
G7 = position 5
We need the LOOKUP function to tell INDEX we want the last instance of 1
from cell G5 which is in position 3. So, back to the result_vector of the
LOOKUP function:
ROW(G3:G7)-MIN(ROW(G3:G7))+1)
We need to convert the row numbers of the range G3:G7 so that they match the
position numbers of the INDEX function. So, we need to make:
3 = 1
4 = 2
5 = 3
6 = 4
7 = 5
We do that using this which is our result_vector:
ROW(G3:G7)-MIN(ROW(G3:G7))+1)
This is how that breaks down:
ROW(G3)-MIN(ROW(G3))+1 = 3-3+1 = 1
ROW(G4)-MIN(ROW(G3))+1 = 4-3+1 = 2
ROW(G5)-MIN(ROW(G3))+1 = 5-3+1 = 3
ROW(G6)-MIN(ROW(G3))+1 = 6-3+1 = 4
ROW(G7)-MIN(ROW(G3))+1 = 7-3+1 = 5
Now we have a result_vector that corresponds to the position numbers of the
INDEX function.
At this point the LOOKUP function looks like this:
LOOKUP(2,{#DIV/0!;1;1;#DIV/0!;#DIV/0!},{1;2;3;4;5})
Now remember, if the lookup_value is greater than any value in the
lookup_vector it will match the *last* value in the lookup_vector that is
*less* than the lookup_value. The *last* value in the lookup_vector that is
*less* than the lookup_value is the 2nd 1. The #DIV/0! errors are ignored.
The result of the LOOKUP function is the value from the result_vector that
corresponds to the 2nd 1. Here's what it looks like vertically:
#DIV/0!.....1
1................2
1................3
#DIV/0!.....4
#DIV/0!.....5
So the result of LOOKUP is 3.
At this point here's what the entire formula looks like:
=COUNTIF(G7:INDEX(G3:G7,3),0)
INDEX returns a reference and our indexed range is:
G3 = position 1
G4 = position 2
G5 = position 3
G6 = position 4
G7 = position 3
We used the LOOKUP function to tell INDEX we want position 3 which is G5 so
we get this:
=COUNTIF(G7:G5,0)
Since Excel works from left to right and from top to bottom it automatically
corrects the range to:
=COUNTIF(G5:G7,0)
And that's how we start with this:
=COUNTIF(G7:INDEX(G3:G7,LOOKUP(2,1/(G3:G7=1),ROW(G3:G7)-MIN(ROW(G3:G7))+1)),0)
And essentially end up with this:
=COUNTIF(G5:G7,0)
=2
That dang LOOKUP function can be pretty confusing to understand but once you
do "get it" it's a piece of cake and can be very powerful.
exp101
--
Biff
Microsoft Excel MVP
SwearBear said:
Biff,
In any given column (of many) I have data that is represented by either
a
zero (0) or a one (1), such as this:
M4 = 0
M5 = 1
M6 = 0
M7 = 0
M8 = 1
M9 = 0
M10 = 0
M11 = 0
M12 = 1
M13 = 1
M14 = Blank/Empty
M15 = Total of '1's
I would like to see the count of the smallest number of '0's between
cells containing '1's: In this case it would be zero (0) because there
are
no '0's between M12-M13. I wouldn't want the count (2) between, say,
M5-M8
(not inclusive) because it's not the smallest count/distance between two
cells containing '1's - M12-M13 is. But there will be many instances of
cells containing '1's NOT being adjacent to one another.
Otherwise, I would like to count the largest number of '0's ocurring
between cells containing '1's: In this case it would be three (3) because
there are three '0's between cells M8-M12 (not inclusive), which is the
largest group of '0's in this column. Can these also be done with
formulae?
In both instances I would like the option of being able to treat the
header row, M3, as if it had a '1' in it. Is that asking too much? There
is
always information in the header row, but it is numerical, starting with
'1',
'2', '3', etc.
I'm really looking forward to reading your "deluxe" explanation when you
have the time to post it.
Thanks, again...
