Countif maybe or Lookup??
 

Hey,

I'm looking for some help with the following problem. In ColumnA, from row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking for a
formula that will scan through the list of numbers in ColA and find all the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another 1.
Thanks for the help,
Matt

Countif maybe or Lookup??
 

Try this:

=SUMPRODUCT((A1:A20=1)*(A2:A21=1))

Adjust range references to suit your situation.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"mpenkala" wrote in message
...
Hey,

I'm looking for some help with the following problem. In ColumnA, from
row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking for
a
formula that will scan through the list of numbers in ColA and find all
the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another 1.
Thanks for the help,
Matt

Countif maybe or Lookup??
 

How about a nice macro
Sub ifonefollowedbyone()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(i, "a") = 1 And Cells(i - 1, "a") = 1 Then _
MsgBox Cells(i, "a").Address
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mpenkala" wrote in message
...
Hey,

I'm looking for some help with the following problem. In ColumnA, from
row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking for
a
formula that will scan through the list of numbers in ColA and find all
the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another 1.
Thanks for the help,
Matt

Countif maybe or Lookup??
 

=SUMPRODUCT(--(A3:A199=1),--(A4:A200=1))
--this will tell how many pairs of 1's there are in A3:A200

"mpenkala" wrote:

Hey,

I'm looking for some help with the following problem. In ColumnA, from row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking for a
formula that will scan through the list of numbers in ColA and find all the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another 1.
Thanks for the help,
Matt

Countif maybe or Lookup??
 

Hi Ron,

This worked great except for the 0 pairs. When I enter
=SUMPRODUCT(($A$3:$A$999=0)*($A$4:$A$1000=0))
It counts all 0's followed by 0's like it should, but also counts all blank
cells followed by another blank cell. Any way around this? Or do I just
adjust my range?

Thanks,
Matt


"Ron Coderre" wrote:

Try this:

=SUMPRODUCT((A1:A20=1)*(A2:A21=1))

Adjust range references to suit your situation.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"mpenkala" wrote in message
...
Hey,

I'm looking for some help with the following problem. In ColumnA, from
row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking for
a
formula that will scan through the list of numbers in ColA and find all
the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another 1.
Thanks for the help,
Matt

Countif maybe or Lookup??
 

Hmmm....You only mentioned that you wanted to count pairs of 1's.

For consecutive zeros, try this:

=SUMPRODUCT(($A$3:$A$999<"")*($A$3:$A$999=0)*($A$ 4:$A$1000=0)*($A$4:$A$1000<""))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"mpenkala" wrote in message
...
Hi Ron,

This worked great except for the 0 pairs. When I enter
=SUMPRODUCT(($A$3:$A$999=0)*($A$4:$A$1000=0))
It counts all 0's followed by 0's like it should, but also counts all
blank
cells followed by another blank cell. Any way around this? Or do I just
adjust my range?

Thanks,
Matt


"Ron Coderre" wrote:

Try this:

=SUMPRODUCT((A1:A20=1)*(A2:A21=1))

Adjust range references to suit your situation.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"mpenkala" wrote in message
...
Hey,

I'm looking for some help with the following problem. In ColumnA, from
row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking
for
a
formula that will scan through the list of numbers in ColA and find all
the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another
1.
Thanks for the help,
Matt

Countif maybe or Lookup??
 

Perfect Ron, works great.

Thanks a bunch.
Matt


"Ron Coderre" wrote:

Hmmm....You only mentioned that you wanted to count pairs of 1's.

For consecutive zeros, try this:

=SUMPRODUCT(($A$3:$A$999<"")*($A$3:$A$999=0)*($A$ 4:$A$1000=0)*($A$4:$A$1000<""))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"mpenkala" wrote in message
...
Hi Ron,

This worked great except for the 0 pairs. When I enter
=SUMPRODUCT(($A$3:$A$999=0)*($A$4:$A$1000=0))
It counts all 0's followed by 0's like it should, but also counts all
blank
cells followed by another blank cell. Any way around this? Or do I just
adjust my range?

Thanks,
Matt


"Ron Coderre" wrote:

Try this:

=SUMPRODUCT((A1:A20=1)*(A2:A21=1))

Adjust range references to suit your situation.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"mpenkala" wrote in message
...
Hey,

I'm looking for some help with the following problem. In ColumnA, from
row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking
for
a
formula that will scan through the list of numbers in ColA and find all
the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another
1.
Thanks for the help,
Matt

Countif maybe or Lookup??
 

Hi mpenkala,

If you were just trying to find them you could use a conditional format or
create an adjacent column with a formula to return TRUE and FALSE next to
each.

The formula for both cases would be something like:

=AND(OR(A1=A2,A2=A3),A2=$C$1)

Where the entry in C1 is the value you want to find if repeated in adjacent
cells.


--
Cheers,
Shane Devenshire


"mpenkala" wrote:

Hey,

I'm looking for some help with the following problem. In ColumnA, from row
3 to 200 I have random numbers ranging between 0 and 50. I'm looking for a
formula that will scan through the list of numbers in ColA and find all the
cell's with "1" in it AND is also followed by "1".

Ex.

ColA
2
0
22
21
45
12
16
6
5
0
0
2
1
19
50
1
1
17
16
28
44

So in this example I would have 1 case of a 1 being followed by another 1.
Thanks for the help,
Matt