Excel Function - Please help
 

From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use it in
a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!

Excel Function - Please help
 

Neil,
you can use this array formula (commit with Ctrl+Shift+Enter)

=ADDRESS(MAX(IF(A1:A1000<0,ROW(A1:A1000)),_column _)

For _column use the number of the column where data is located.

HTH
Kostis Vezerides

Neil Hindry wrote:
From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use it in
a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!

Excel Function - Please help
 

Hi Neil
One way
=MAX(MATCH(LOOKUP(99^99,A:A),A:A),MATCH(LOOKUP(REP T("z",255),A:A),A:A))


--
Regards

Roger Govier


"Neil Hindry" wrote in message
...
From a cell I want to find out what is the first cell going upwards in
the same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use
it in a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!

Excel Function - Please help
 

You're right, DL..Thanks for spotting the error......The formulas return the
value, not the reference.

My first post does the job.
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20))))

or

The formula reference variation....
=INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20)))

as proven by =ROW(INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20))))


***********
Regards,
Ron

XL2002, WinXP


"daddylonglegs" wrote:

I don't think these will work, Ron. Wouldn't you need MATCH not LOOKUP?, i.e.

=INDEX(A1:A20,MATCH(2,1/(A1:A20<0)))

confirmed with CTRL+SHIFT+ENTER

"Ron Coderre" wrote:

Actually.....these are shorter:

For the cell address:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20)))

or....to be used in a formula (as you mentioned):
=INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

To deteremine the address of the LAST non-blank, non-zero value above cell A21

Try this:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20))))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Neil Hindry" wrote:

From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use it in
a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!

Excel Function - Please help
 

Actually.....these are shorter:

For the cell address:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20)))

or....to be used in a formula (as you mentioned):
=INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

To deteremine the address of the LAST non-blank, non-zero value above cell A21

Try this:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20))))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Neil Hindry" wrote:

From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use it in
a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!

Excel Function - Please help
 

Assuming your formula is in A100 try this

=INDEX(A$1:A99,MAX(IF(A$1:A99<0,ROW(A$1:A99)-ROW(A$1)+1)))

confirmed with CTRL+SHIFT+ENTER

Note that if you use this formula on its own it will return the last value
in the column (that isn't zero) BUT used within a formula it WILL return the
cell reference as requested

"Neil Hindry" wrote:

From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use it in
a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!

Excel Function - Please help
 

I don't think these will work, Ron. Wouldn't you need MATCH not LOOKUP?, i.e.

=INDEX(A1:A20,MATCH(2,1/(A1:A20<0)))

confirmed with CTRL+SHIFT+ENTER

"Ron Coderre" wrote:

Actually.....these are shorter:

For the cell address:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20)))

or....to be used in a formula (as you mentioned):
=INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

To deteremine the address of the LAST non-blank, non-zero value above cell A21

Try this:
=CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20))))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Neil Hindry" wrote:

From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value.
I don't want to know the value I want the cell reference so I can use it in
a formula.

Does anyone know how I can do this in Excel 2003?

I hope you can help me.

I appreciate any help or information given.


Thanks!