Counting from the Bottom Up
 

I'm new to these Excel Discussion Boards/Forums as of yesterday, so please
bear with me. I've looked at hundreds of topics and many hundreds of posts,
but haven't found anything that gives me a clue as to how to go about dealing
with this problem:

I have multiple, adjacent columns, in the middle of a sheet, where the
values returned in the cells in these columns are either True or False. I
would like to be able to count up, from the last cell in a (ea.) column
(e.g.- G30:G3) until I reach a value of True, and then return that count
(total number of False or 0 values) into a cell in the same column, several
cells below the last True or False value. I have no need to continue
counting in a given column after that. Each column will have different
locations for the T/F values. Some may show the first value as True, whereas
another may count up 30 or 40 cells with False values before coming to a
True. From what I've read to date, I believe this could be accomplished with
a looping-type formula, using an "x = x - 1" operation to go up one cell at a
time, rather than (having to resort to) a macro, but I'm not sure how to put
it together. Perhaps an If...Then or Do...While/Until situation? or a
Count(If)/Countif? I think I know just enough to be dangerous, but not
enough to figure out even how to start this formula, if in fact, it can be
done with a formula &/or functions. Again, I'm really trying to avoid using
a macro. Any help would be appreciated. Good help would be revered.
--
Sociopath

Counting from the Bottom Up
 

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0. How
about *empty* cells, any of those? Better yet, post a small example and tell
us what result you expect.


--
Biff
Microsoft Excel MVP


"SwearBear" wrote in message
...
I'm new to these Excel Discussion Boards/Forums as of yesterday, so please
bear with me. I've looked at hundreds of topics and many hundreds of
posts,
but haven't found anything that gives me a clue as to how to go about
dealing
with this problem:

I have multiple, adjacent columns, in the middle of a sheet, where the
values returned in the cells in these columns are either True or False. I
would like to be able to count up, from the last cell in a (ea.) column
(e.g.- G30:G3) until I reach a value of True, and then return that count
(total number of False or 0 values) into a cell in the same column,
several
cells below the last True or False value. I have no need to continue
counting in a given column after that. Each column will have different
locations for the T/F values. Some may show the first value as True,
whereas
another may count up 30 or 40 cells with False values before coming to a
True. From what I've read to date, I believe this could be accomplished
with
a looping-type formula, using an "x = x - 1" operation to go up one cell
at a
time, rather than (having to resort to) a macro, but I'm not sure how to
put
it together. Perhaps an If...Then or Do...While/Until situation? or a
Count(If)/Countif? I think I know just enough to be dangerous, but not
enough to figure out even how to start this formula, if in fact, it can be
done with a formula &/or functions. Again, I'm really trying to avoid
using
a macro. Any help would be appreciated. Good help would be revered.
--
Sociopath

Counting from the Bottom Up
 

hi, !

not sure i'm following you correctly -?-
assuming in your range [G3:30] are at lest two TRUE (real true values)
and you need to count (from bottom to up) how many "non-true" cells...

array-entered formulae (ctrl + shift + enter)

op1: =sum(large(if(g3:g30,row(g3:g30)),{1;2})*{1;-1})-1
op2: =large(if(g3:g30,row(g3:g30)),1)-large(if(g3:g30,row(g3:g30)),2)-1

sorry if i don't undestand your complete scenario

hth,
hector.

I'm new to these Excel Discussion Boards/Forums as of yesterday, so please
bear with me. I've looked at hundreds of topics and many hundreds of posts,
but haven't found anything that gives me a clue as to how to go about dealing
with this problem:

I have multiple, adjacent columns, in the middle of a sheet, where the
values returned in the cells in these columns are either True or False. I
would like to be able to count up, from the last cell in a (ea.) column
(e.g.- G30:G3) until I reach a value of True, and then return that count
(total number of False or 0 values) into a cell in the same column, several
cells below the last True or False value. I have no need to continue
counting in a given column after that. Each column will have different
locations for the T/F values. Some may show the first value as True, whereas
another may count up 30 or 40 cells with False values before coming to a
True. From what I've read to date, I believe this could be accomplished with
a looping-type formula, using an "x = x - 1" operation to go up one cell at a
time, rather than (having to resort to) a macro, but I'm not sure how to put
it together. Perhaps an If...Then or Do...While/Until situation? or a
Count(If)/Countif? I think I know just enough to be dangerous, but not
enough to figure out even how to start this formula, if in fact, it can be
done with a formula &/or functions. Again, I'm really trying to avoid using
a macro. Any help would be appreciated. Good help would be revered.
--
Sociopath

Counting from the Bottom Up
 

They are "0" & "1" values returned from an "=IF(OR(****),1,0) formula. I
think that makes them numerical values that are/can be treated as logicals??
Actually, most of the cells contain zeros. There are no *empty* cells. The
cells/columns are already formated to center. This spreadsheet is setup to
add data in a newly inserted row everytime there is new data to add. I want
to be able to count from the bottom/inserted row upwards to the first
instance of a "1". I'm not sure how to "post a small example", unless that's
what I just did at the top?? I'm a total neophyte and may have to ask for
many clarifications...
--
Sociopath


"T. Valko" wrote:

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0. How
about *empty* cells, any of those? Better yet, post a small example and tell
us what result you expect.


--
Biff
Microsoft Excel MVP

Counting from the Bottom Up
 

It may give you more of the information you're asking about if you look at
the post I just made to T. Valko. Pleaase let me know.
--
Sociopath


"Héctor Miguel" wrote:

hi, !

not sure i'm following you correctly -?-
assuming in your range [G3:30] are at lest two TRUE (real true values)
and you need to count (from bottom to up) how many "non-true" cells...

array-entered formulae (ctrl + shift + enter)

op1: =sum(large(if(g3:g30,row(g3:g30)),{1;2})*{1;-1})-1
op2: =large(if(g3:g30,row(g3:g30)),1)-large(if(g3:g30,row(g3:g30)),2)-1

sorry if i don't undestand your complete scenario

hth,
hector.

Counting from the Bottom Up
 

To additionally clarify: "=IF(OR(****),1,0) should have had a closing quote
on the end - I forgot it. The quotes are not in the formula itself, just in
my post. Also, the newly inserted data/row is added below the last/previous
data/row, not somewhere in the middle of the sheet. Hope that helps a little.
--
Sociopath


"T. Valko" wrote:

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0. How
about *empty* cells, any of those? Better yet, post a small example and tell
us what result you expect.


--
Biff
Microsoft Excel MVP

Counting from the Bottom Up
 

Try this:

=COUNTIF(G30:INDEX(G3:G30,LOOKUP(2,1/(G3:G30=1),ROW(G3:G30)-MIN(ROW(G3:G30))+1)),0)

Or, this slightly shorter array formula** .

If there will *never* be a 1 G1:G2 we can use some shortcuts.

=SUM(IF(G30:INDEX(G:G,MAX((G1:G30=1)*ROW(G1:G30))) =0,1))

Note that the array formula will count *empty* cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"SwearBear" wrote in message
...
They are "0" & "1" values returned from an "=IF(OR(****),1,0) formula. I
think that makes them numerical values that are/can be treated as
logicals??
Actually, most of the cells contain zeros. There are no *empty* cells.
The
cells/columns are already formated to center. This spreadsheet is setup
to
add data in a newly inserted row everytime there is new data to add. I
want
to be able to count from the bottom/inserted row upwards to the first
instance of a "1". I'm not sure how to "post a small example", unless
that's
what I just did at the top?? I'm a total neophyte and may have to ask for
many clarifications...
--
Sociopath


"T. Valko" wrote:

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals
appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0.
How
about *empty* cells, any of those? Better yet, post a small example and
tell
us what result you expect.


--
Biff
Microsoft Excel MVP

Counting from the Bottom Up
 

Biff(?),

Thank you SO MUCH for your answers to my question. The formula you
posted worked PERFECTLY! Unfortunately, it is more complex (to me) than I
can easily follow. Is there any chance you could walk me through it so I can
more clearly understand it and just what each operation is doing? Either
way, I intend to review each of the operators as soon as I have the time so I
can see how they relate to my spreadsheet situation.
I did not try to use your array formulay for three reasons: 1 - I have a
header in Row 3, so there will always be something in R1, R2, &/or R3; 2 - I
have an empty row between the last row of data entered and the Totals row so
that when I insert a new row for the new data, I will maintain my formatting
and inclusion of the new data in the formula parameters. That means that I
will ALWAYS have an empty cell as the last cell in the data row. And; 3 - I
don't understand the operation of the array formula well enough to see
exactly what it is doing! (Care to walk me through that one, too?)
I have two other issues with these same columns: 1 - I'd like to be able
to use a formula to get the count of the minimum number of "0"s between the
"1"s, and; 2 - Conversely, I'd like to be able to use a formula to get the
count of the maximum number of "0"s between the "1"s. I can't figure out how
to put a formula(s) together to do this, either. Are you interested, or
should I post this/these as (a) separate topic(s)?
As I mentioned initially, I'm new to this whole process and ignorant of
some of the details. Although I have clicked on the "Yes" button, I don't
see any option to "rate a post" other than the question/link. Should I?
Also, how does a post get assigned an "Answer" icon (the check mark in the
little, green circle?
Again, thanks more than I can say for saving me so much time stumbling
around to try and figure out the right formula to use. I was pretty certain
I wouldn't have to use a macro. You've totally amazed me by posting the
correct formula for this situation so quickly! I couldn't find anything
similar in all the help files I've read, nor the hundreds of postings, and I
thought it was going to be a tough nut to crack. You've made it look easy,
but that's what the top people in any field always do. I'm impressed.
--
Sociopath


"T. Valko" wrote:

Try this:

=COUNTIF(G30:INDEX(G3:G30,LOOKUP(2,1/(G3:G30=1),ROW(G3:G30)-MIN(ROW(G3:G30))+1)),0)

Or, this slightly shorter array formula** .

If there will *never* be a 1 G1:G2 we can use some shortcuts.

=SUM(IF(G30:INDEX(G:G,MAX((G1:G30=1)*ROW(G1:G30))) =0,1))

Note that the array formula will count *empty* cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

Counting from the Bottom Up
 

Héctor,

Your formulas return incorrect values for the problem. Please see the
previous post from T. Valko and my response to his post, as well as my
previous response to yours. I really appreciate your efforts and they have
given me some additional ideas to possibly help me with other problems, so
thanks very much for that.
--
Sociopath


"Héctor Miguel" wrote:

hi, !

not sure i'm following you correctly -?-
assuming in your range [G3:30] are at lest two TRUE (real true values)
and you need to count (from bottom to up) how many "non-true" cells...

array-entered formulae (ctrl + shift + enter)

op1: =sum(large(if(g3:g30,row(g3:g30)),{1;2})*{1;-1})-1
op2: =large(if(g3:g30,row(g3:g30)),1)-large(if(g3:g30,row(g3:g30)),2)-1

sorry if i don't undestand your complete scenario

hth,
hector.

Counting from the Bottom Up
 

hi, !

Your formulas return incorrect values for the problem.
Please see the previous post from T. Valko and my response to his post
as well as my previous response to yours.
I really appreciate your efforts and they have given me some additional ideas
to possibly help me with other problems, so thanks very much for that.

don't worry, "my" proposed formulae were made with assumptions on your data-type & arrangement
and i saw your response/s but, with Tony you are in the best hands (than mine)

if my "wild" ideas gives to you additional ideas (for other situations)... i'm glad for that
so, thanks to you (for your feed-back), i really appreciate it ;)

regards,
hector.

Counting from the Bottom Up
 

I'll explain the formula later when I have more time. I tend to write
"deluxe" explanations and I'm a slow one finger typer so it takes me a long
time!

As for this stuff:

I have two other issues with these same columns:
1 - I'd like to be able to use a formula to get the
count of the minimum number of "0"s between the
"1"s, and; 2 - Conversely, I'd like to be able to use
a formula to get the count of the maximum number
of "0"s between the "1"s.

I'll need to see an example and the expected results.

--
Biff
Microsoft Excel MVP


"SwearBear" wrote in message
...
Biff(?),

Thank you SO MUCH for your answers to my question. The formula you
posted worked PERFECTLY! Unfortunately, it is more complex (to me) than I
can easily follow. Is there any chance you could walk me through it so I
can
more clearly understand it and just what each operation is doing? Either
way, I intend to review each of the operators as soon as I have the time
so I
can see how they relate to my spreadsheet situation.
I did not try to use your array formulay for three reasons: 1 - I have
a
header in Row 3, so there will always be something in R1, R2, &/or R3; 2 -
I
have an empty row between the last row of data entered and the Totals row
so
that when I insert a new row for the new data, I will maintain my
formatting
and inclusion of the new data in the formula parameters. That means that
I
will ALWAYS have an empty cell as the last cell in the data row. And; 3 -
I
don't understand the operation of the array formula well enough to see
exactly what it is doing! (Care to walk me through that one, too?)
I have two other issues with these same columns: 1 - I'd like to be
able
to use a formula to get the count of the minimum number of "0"s between
the
"1"s, and; 2 - Conversely, I'd like to be able to use a formula to get the
count of the maximum number of "0"s between the "1"s. I can't figure out
how
to put a formula(s) together to do this, either. Are you interested, or
should I post this/these as (a) separate topic(s)?
As I mentioned initially, I'm new to this whole process and ignorant of
some of the details. Although I have clicked on the "Yes" button, I don't
see any option to "rate a post" other than the question/link. Should I?
Also, how does a post get assigned an "Answer" icon (the check mark in the
little, green circle?
Again, thanks more than I can say for saving me so much time stumbling
around to try and figure out the right formula to use. I was pretty
certain
I wouldn't have to use a macro. You've totally amazed me by posting the
correct formula for this situation so quickly! I couldn't find anything
similar in all the help files I've read, nor the hundreds of postings, and
I
thought it was going to be a tough nut to crack. You've made it look
easy,
but that's what the top people in any field always do. I'm impressed.
--
Sociopath


"T. Valko" wrote:

Try this:

=COUNTIF(G30:INDEX(G3:G30,LOOKUP(2,1/(G3:G30=1),ROW(G3:G30)-MIN(ROW(G3:G30))+1)),0)

Or, this slightly shorter array formula** .

If there will *never* be a 1 G1:G2 we can use some shortcuts.

=SUM(IF(G30:INDEX(G:G,MAX((G1:G30=1)*ROW(G1:G30))) =0,1))

Note that the array formula will count *empty* cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

Counting from the Bottom Up
 

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...
--
Sociopath


"T. Valko" wrote:

I'll explain the formula later when I have more time. I tend to write
"deluxe" explanations and I'm a slow one finger typer so it takes me a long
time!

As for this stuff:

I have two other issues with these same columns:
1 - I'd like to be able to use a formula to get the
count of the minimum number of "0"s between the
"1"s, and; 2 - Conversely, I'd like to be able to use
a formula to get the count of the maximum number
of "0"s between the "1"s.

I'll need to see an example and the expected results.

--
Biff
Microsoft Excel MVP

Counting from the Bottom Up
 

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" wrote in message
...
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...
--
Sociopath


"T. Valko" wrote:

I'll explain the formula later when I have more time. I tend to write
"deluxe" explanations and I'm a slow one finger typer so it takes me a
long
time!

As for this stuff:

I have two other issues with these same columns:
1 - I'd like to be able to use a formula to get the
count of the minimum number of "0"s between the
"1"s, and; 2 - Conversely, I'd like to be able to use
a formula to get the count of the maximum number
of "0"s between the "1"s.

I'll need to see an example and the expected results.

--
Biff
Microsoft Excel MVP

Counting from the Bottom Up
 

Hi Biff,

that is SOME explanation - well done !!

(Makes my =E2+C3-D3 suggestion to another post look puny) <bg

Pete

On Aug 14, 6:15*am, "T. Valko" wrote:
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" wrote in message

...



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...
--
Sociopath

"T. Valko" wrote:

I'll explain the formula later when I have more time. I tend to write
"deluxe" explanations and I'm a slow one finger typer so it takes me a
long
time!

As for this stuff:

I have two other issues with these same columns:
1 - I'd like to be able to use a formula to get the
count of the minimum number of "0"s between the
"1"s, and; 2 - Conversely, I'd like to be able to use
a formula to get the count of the maximum number
of "0"s between the "1"s.

I'll need to see an example and the expected results.

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Counting from the Bottom Up
 

Thanks, Pete!

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
Hi Biff,

that is SOME explanation - well done !!

(Makes my =E2+C3-D3 suggestion to another post look puny) <bg

Pete

On Aug 14, 6:15 am, "T. Valko" wrote:
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" wrote in message

...



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...
--
Sociopath

"T. Valko" wrote:

I'll explain the formula later when I have more time. I tend to write
"deluxe" explanations and I'm a slow one finger typer so it takes me a
long
time!

As for this stuff:

I have two other issues with these same columns:
1 - I'd like to be able to use a formula to get the
count of the minimum number of "0"s between the
"1"s, and; 2 - Conversely, I'd like to be able to use
a formula to get the count of the maximum number
of "0"s between the "1"s.

I'll need to see an example and the expected results.

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -