Find first positive value in a column
 

I am trying to create a formula that will:
1. Search a column of numbers to find the first row with a positive number
2. Once found, I need to reference another column on the same row which
contains a date.
3. Finally, subtract that date from a fixed date (Jan 1, 2007) to end up
with a number.

I'm fairly new to Excel and wonder if this can be done with a formula or if
I need to get into VB script (hope not) .
Any help would be appreciated.

Find first positive value in a column
 

Try this array formula** :

A1 = 1/1/2007

B5:B20 = numbers
D5:D20 = dates

=A1-INDEX(D5:D20,MATCH(TRUE,A5:A200,0))

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

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
I am trying to create a formula that will:
1. Search a column of numbers to find the first row with a positive number
2. Once found, I need to reference another column on the same row which
contains a date.
3. Finally, subtract that date from a fixed date (Jan 1, 2007) to end up
with a number.

I'm fairly new to Excel and wonder if this can be done with a formula or
if
I need to get into VB script (hope not) .
Any help would be appreciated.

Find first positive value in a column
 

You mean:
=A1-INDEX(D5:D20,MATCH(TRUE,B5:B200,0))
(not A5:A20)
Bob Umlas

"T. Valko" wrote in message
...
Try this array formula** :

A1 = 1/1/2007

B5:B20 = numbers
D5:D20 = dates

=A1-INDEX(D5:D20,MATCH(TRUE,A5:A200,0))

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

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
I am trying to create a formula that will:
1. Search a column of numbers to find the first row with a positive
number
2. Once found, I need to reference another column on the same row which
contains a date.
3. Finally, subtract that date from a fixed date (Jan 1, 2007) to end up
with a number.

I'm fairly new to Excel and wonder if this can be done with a formula or
if
I need to get into VB script (hope not) .
Any help would be appreciated.

Find first positive value in a column
 

Thanks for straightening that out!

--
Biff
Microsoft Excel MVP


"Bob Umlas" wrote in message
...
You mean:
=A1-INDEX(D5:D20,MATCH(TRUE,B5:B200,0))
(not A5:A20)
Bob Umlas

"T. Valko" wrote in message
...
Try this array formula** :

A1 = 1/1/2007

B5:B20 = numbers
D5:D20 = dates

=A1-INDEX(D5:D20,MATCH(TRUE,A5:A200,0))

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

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
I am trying to create a formula that will:
1. Search a column of numbers to find the first row with a positive
number
2. Once found, I need to reference another column on the same row which
contains a date.
3. Finally, subtract that date from a fixed date (Jan 1, 2007) to end up
with a number.

I'm fairly new to Excel and wonder if this can be done with a formula or
if
I need to get into VB script (hope not) .
Any help would be appreciated.

Find first positive value in a column
 

That worked perfectly. Thanks very much!

"T. Valko" wrote:

Thanks for straightening that out!

--
Biff
Microsoft Excel MVP


"Bob Umlas" wrote in message
...
You mean:
=A1-INDEX(D5:D20,MATCH(TRUE,B5:B200,0))
(not A5:A20)
Bob Umlas

"T. Valko" wrote in message
...
Try this array formula** :

A1 = 1/1/2007

B5:B20 = numbers
D5:D20 = dates

=A1-INDEX(D5:D20,MATCH(TRUE,A5:A200,0))

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

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
I am trying to create a formula that will:
1. Search a column of numbers to find the first row with a positive
number
2. Once found, I need to reference another column on the same row which
contains a date.
3. Finally, subtract that date from a fixed date (Jan 1, 2007) to end up
with a number.

I'm fairly new to Excel and wonder if this can be done with a formula or
if
I need to get into VB script (hope not) .
Any help would be appreciated.

Find first positive value in a column
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
That worked perfectly. Thanks very much!

"T. Valko" wrote:

Thanks for straightening that out!

--
Biff
Microsoft Excel MVP


"Bob Umlas" wrote in message
...
You mean:
=A1-INDEX(D5:D20,MATCH(TRUE,B5:B200,0))
(not A5:A20)
Bob Umlas

"T. Valko" wrote in message
...
Try this array formula** :

A1 = 1/1/2007

B5:B20 = numbers
D5:D20 = dates

=A1-INDEX(D5:D20,MATCH(TRUE,A5:A200,0))

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

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
I am trying to create a formula that will:
1. Search a column of numbers to find the first row with a positive
number
2. Once found, I need to reference another column on the same row
which
contains a date.
3. Finally, subtract that date from a fixed date (Jan 1, 2007) to end
up
with a number.

I'm fairly new to Excel and wonder if this can be done with a formula
or
if
I need to get into VB script (hope not) .
Any help would be appreciated.