How to get LAST value of the column

[hsandeep]

My values are NUMERICALS (-ve, 0 or +ve) in C2:C9999
In B2, I want LAST value of the column C2:C9999 & it must be +ve.
How to accomplish?
Thanx in adv

 

[Jonmo1]

So you mean the last value above 0 ?

Try
=LOOKUP(2,1/(C2:C9999>0),C2:C9999)

 

[Aladin Akyurek]

My values are NUMERICALS (-ve, 0 or +ve) in C2:C9999
In B2, I want LAST value of the column C2:C9999 & it must be +ve.
How to accomplish?
Thanx in adv

Try...

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>=0)),C2:C9999)

 

[hsandeep]

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>=0)),C2:C9999)[/QUOTE]
It returns 0. I don't want 0 to be returned (& neither -ve).

 

[Aladin Akyurek]

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>=0)),C2:C9999)

It returns 0. I don't want 0 to be returned (& neither -ve).[/QUOTE]

Change the >= test to just the > test...

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>0)),C2:C9999)

 

[hsandeep]

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>0)),C2:C9999)[/QUOTE]
It looks for values in C2:C9999 but STOPS once it find a 0 & returns the last found value. C2=100, C3=200 & C4=0, C5=786. It reurns 200 (WRONG) 786 (CORRECT)

 

[Aladin Akyurek]

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>0)),C2:C9999)

It looks for values in C2:C9999 but STOPS once it find a 0 & returns the last found value. C2=100, C3=200 & C4=0, C5=786. It reurns 200 (WRONG) 786 (CORRECT)[/QUOTE]

Row\Col	C​	D​	E​
1​
2​			9​
3​
4​	-4​
5​	-3​
6​	6​
7​	0​
8​
9​	9​
10​
11​	-2​
12​

E2:

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>0)),C2:C9999)

 

[hsandeep]

It looks for values in C2:C9999 but STOPS once it find a 0 & returns the last found value. C2=100, C3=200 & C4=0, C5=786. It reurns 200 (WRONG) 786 (CORRECT)

E2:

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>0)),C2:C9999)[/QUOTE]

IT WORKS. I'll check again & inform you tomorrow as well.

 

[Jonmo1]

Either formula suggested works just fine for me.

Aladin's is more error proof as it accounts for text entries in the range.
I left that out of mine because you said your range was numeric..

E2: =LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>0)),C2:C9999)
F2: =LOOKUP(2,1/(C2:C9999>0),C2:C9999)

Excel 2010
	C	D	E	F
2	100		786	786
3	200
4	0
5	786
Sheet1

 

[hsandeep]

=LOOKUP(9.99999999999999E+307,1/(ISNUMBER(C2:C9999)*(C2:C9999>0)),C2:C9999)[/QUOTE]

IT WORKS. I'll check again & inform you tomorrow as well.[/QUOTE]

IT WORKS. Thanks