I am trying to search a column (C5:C400) to find out when it runs to a null
value, then search down a different column (G) starting from that same row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

You have to be *very* precise in your terminology!

What *exactly* do you mean by "null value"?
Empty (blank, unused) cell
Zero length string ( "" )
0 in cell
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to a null
value, then search down a different column (G) starting from that same row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to a null
value, then search down a different column (G) starting from that same row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

By null value, I mean the cell will be Empty (blank, unused).
Thanks,

"RagDyer" wrote:

You have to be *very* precise in your terminology!

What *exactly* do you mean by "null value"?
Empty (blank, unused) cell
Zero length string ( "" )
0 in cell
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to a null
value, then search down a different column (G) starting from that same row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Hi Peo,
Thanks, but the formula below returns the first value in column C (after row
5), but I am actually looking for the first positive value in column G after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to a null
value, then search down a different column (G) starting from that same row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Then this should work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)


array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
By null value, I mean the cell will be Empty (blank, unused).
Thanks,

"RagDyer" wrote:

You have to be *very* precise in your terminology!

What *exactly* do you mean by "null value"?
Empty (blank, unused) cell
Zero length string ( "" )
0 in cell
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to a
null
value, then search down a different column (G) starting from that same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

I misunderstood, I thought you were looking for zero in column G, this will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C (after
row
5), but I am actually looking for the first positive value in column G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to a
null
value, then search down a different column (G) starting from that same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point, if it
helps). I need the first positive number in column G after column C is empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G, this will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C (after
row
5), but I am actually looking for the first positive value in column G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to a
null
value, then search down a different column (G) starting from that same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point, but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Either I totally misunderstood what you wanted or you are not using the same
cell references/formula as posted because it does return the first value in
C5:C400 greater than zero starting from the first blank cell in G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28 and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point, if it
helps). I need the first positive number in column G after column C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G, this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C
(after
row
5), but I am actually looking for the first positive value in column G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to
a
null
value, then search down a different column (G) starting from that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

I am looking for the first positive value in column G (not C), after the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using the same
cell references/formula as posted because it does return the first value in
C5:C400 greater than zero starting from the first blank cell in G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28 and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point, if it
helps). I need the first positive number in column G after column C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G, this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C
(after
row
5), but I am actually looking for the first positive value in column G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs to
a
null
value, then search down a different column (G) starting from that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using the
same
cell references/formula as posted because it does return the first value
in
C5:C400 greater than zero starting from the first blank cell in G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28 and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point, if
it
helps). I need the first positive number in column G after column C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G, this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C
(after
row
5), but I am actually looking for the first positive value in column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs
to
a
null
value, then search down a different column (G) starting from that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

It now shows #N/A. Could it be because Column G has blank (empty cell) rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using the
same
cell references/formula as posted because it does return the first value
in
C5:C400 greater than zero starting from the first blank cell in G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28 and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point, if
it
helps). I need the first positive number in column G after column C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G, this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C
(after
row
5), but I am actually looking for the first positive value in column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it runs
to
a
null
value, then search down a different column (G) starting from that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

No, sounds like you forgot to enter it with ctrl + shift & enter. I have a
small sample I could email you if you provide me with an email address but
make sure you disguise it so the spambots don't fish it up



--


Regards,


Peo Sjoblom


"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using
the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting in
C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28
and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point,
if
it
helps). I need the first positive number in column G after column C
is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G,
this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it
runs
to
a
null
value, then search down a different column (G) starting from
that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Try this *array* formula:

=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40,
MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using
the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting in
C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28
and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that point,
if
it
helps). I need the first positive number in column G after column C
is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G,
this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it
runs
to
a
null
value, then search down a different column (G) starting from
that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

That formula does not work for me,it fails if G5 is zero


--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Try this *array* formula:

=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40,
MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after
the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using
the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting
in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28
and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that
point, if
it
helps). I need the first positive number in column G after column C
is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G,
this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column
C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it
runs
to
a
null
value, then search down a different column (G) starting from
that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

I don't know Peo, but I think both our formulas do what they're supposed to
do.
And, putting back the 0's that I took out of the G & C400, they're just
about the same size too!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Peo Sjoblom" wrote in message
...
No, sounds like you forgot to enter it with ctrl + shift & enter. I have a
small sample I could email you if you provide me with an email address but
make sure you disguise it so the spambots don't fish it up



--


Regards,


Peo Sjoblom


"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after
the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using
the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting
in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28
and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that
point, if
it
helps). I need the first positive number in column G after column C
is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G,
this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column
C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it
runs
to
a
null
value, then search down a different column (G) starting from
that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Works OK for me!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
That formula does not work for me,it fails if G5 is zero


--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Try this *array* formula:

=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40,
MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after
the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using
the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting
in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28
and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that
point, if
it
helps). I need the first positive number in column G after column
C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G,
this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column
C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it
runs
to
a
null
value, then search down a different column (G) starting from
that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

So if you have a zero in C5 and the first blank in C22 and you have 3 in G9
and the first positive value let's say 800 in G28 what does it return? For
me it returned 3 while mine returned 800

--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Works OK for me!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
That formula does not work for me,it fails if G5 is zero


--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Try this *array* formula:

=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40,
MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after
the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using
the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting
in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell
C28 and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that
point, if
it
helps). I need the first positive number in column G after column
C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column
G, this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in
column C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it
runs
to
a
null
value, then search down a different column (G) starting
from that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a
starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

That works!

Thank you both very much.

"RagDyer" wrote:

Works OK for me!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
That formula does not work for me,it fails if G5 is zero


--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Try this *array* formula:

=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40,
MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after
the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not using
the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have starting
in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell C28
and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that
point, if
it
helps). I need the first positive number in column G after column
C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column G,
this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in column
C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when it
runs
to
a
null
value, then search down a different column (G) starting from
that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

You first mentioned *G*5, which I checked, and found nothing wrong.

You then mentioned *C*5, which did as you said, where it used the zero cell
as the first reference point.

A simple revision brings it in line with yours:

=INDEX(INDEX(G5:G400,MATCH(TRUE,C5:C400="",0)):G40 0,
MATCH(TRUE,INDEX(G5:G400,MATCH(TRUE,C5:C400="",0)) :G4000,0))

I guess this is what causes bugs, even for MS, where not *all* scenarios are
checked out before releasing the product.

It's nice of you and Biff to check me out most of the time.<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
So if you have a zero in C5 and the first blank in C22 and you have 3 in
G9 and the first positive value let's say 800 in G28 what does it return?
For me it returned 3 while mine returned 800

--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Works OK for me!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
That formula does not work for me,it fails if G5 is zero


--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Try this *array* formula:

=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40,
MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C), after
the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not
using the
same
cell references/formula as posted because it does return the first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have
starting in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell
C28 and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that
point, if
it
helps). I need the first positive number in column G after
column C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column
G, this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in
column C
(after
row
5), but I am actually looking for the first positive value in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am trying to search a column (C5:C400) to find out when
it runs
to
a
null
value, then search down a different column (G) starting
from that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a
starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!

Appreciate the feed-back ...*BUT* ...
If there's *any possibility* that you might have zeroes in Column C,
Make sure that you use either my *revised* formula, or Peo's formula.

Peo's formula *does* work!
Don't know what you might have done, that you had trouble trying it out.

As far as I can tell, *both* formulas do *exactly* the same thing!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
That works!

Thank you both very much.

"RagDyer" wrote:

Works OK for me!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
That formula does not work for me,it fails if G5 is zero


--


Regards,


Peo Sjoblom




"RagDyer" wrote in message
...
Try this *array* formula:

=INDEX(INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G40,
MATCH(TRUE,INDEX(G5:G40,MATCH(TRUE,C5:C40=0,0)):G4 00,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE
when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"George" wrote in message
...
It now shows #N/A. Could it be because Column G has blank (empty
cell)
rows
before the first positive value?

"Peo Sjoblom" wrote:

Try this

=INDEX(G5:G400,MATCH(TRUE,INDEX(INDEX(G5:G400,MATC H(TRUE,C5:C400="",0)):G400,0)0,0)+MATCH(TRUE,C5:C 400="",0)-1)

array entered


--


Regards,


Peo Sjoblom



"George" wrote in message
...
I am looking for the first positive value in column G (not C),
after
the
first empty cell in column C.

(I tried just replacing the C & G's but it didn't work.)

"Peo Sjoblom" wrote:

Either I totally misunderstood what you wanted or you are not
using
the
same
cell references/formula as posted because it does return the
first
value
in
C5:C400 greater than zero starting from the first blank cell in
G5:G500.
This is what I have starting in G5

0
8
7
6
5
4
3
2
1
0
-1
-2
-3
-4
-5
-6
-7

8

9


so the first blank is below -7 in C22, this is what I have
starting
in C5

0
-17
-16
-15
1
-13
-12
16
-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
0
0
0
0
5
6
7
8
9
10


the first value greater than 0 starting in row C22 is 5 in cell
C28
and
that is what my formula returns


--


Regards,


Peo Sjoblom




"George" wrote in message
...
Hi Peo,
Sorry but that returns the C5 value (column G is zero at that
point, if
it
helps). I need the first positive number in column G after
column
C is
empty.

Thanks,


"Peo Sjoblom" wrote:

I misunderstood, I thought you were looking for zero in column
G,
this
will
work

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(TRUE,G5:G400="",0)):C400,0)0,0)+MATCH(TRUE,G5:G 400="",0)-1)



--


Regards,


Peo Sjoblom



"George" wrote in message
...
Hi Peo,
Thanks, but the formula below returns the first value in
column
C
(after
row
5), but I am actually looking for the first positive value
in
column
G
after
column C declines to an empty cell.



"Peo Sjoblom" wrote:

One way

=INDEX(C5:C400,MATCH(TRUE,INDEX(INDEX(C5:C400,MATC H(1,(G5:G400=0)*(G5:G400<""),0)):C400,0)0,0)+MAT CH(1,(G5:G400=0)*(G5:G400<""),0)-1)

entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"George" wrote in
message
...
I am trying to search a column (C5:C400) to find out when
it
runs
to
a
null
value, then search down a different column (G) starting
from
that
same
row
and return the first positive number found.

Can anyone help? I've been using the following as a
starting
point,
but
I'm
stuck on how to complete it.

=INDEX(MATCH(TRUE,G5:G4000),MATCH(TRUE,C5:C400=0, 0))

Thanks!