Find the last positive value including zero

[sguna1982]

Dear Team,

Kindly provide the formula to find the last positive value in given range even "zero" also.

Expected result is "Zero"

15,45,53,924.29
15,44,61,710.04
15,41,40,266.79
15,36,71,986.04
15,35,64,198.54
0.00
0.00
0.00
-200.00
-6,000.00
-1,00,000.00

 

[Bhawani]

Will this be the series from positive to negative or again their are chances of getting positive number again.
Please upload a sample file to make it more clear.

 

[sguna1982]

Yes, there is chance of getting again in the positive values also. like i mentioned, here the expected result is 1000


15,45,53,924.29
15,44,61,710.04
15,41,40,266.79
15,36,71,986.04
15,35,64,198.54
0.00
0.00
0.00
-200.00
-6,000.00
-1,00,000.00
1,000.00

 

[Somendra Misra]

Hi Sguna,

Two Solution:

1. Array formula (Enter with Ctrl+Shift+Enter).

=INDEX($J$1:$J$11,MAX(IF($J$1:$J$11>=0,ROW($J$1:$J$11)-ROW($J$1)+1)))

2. Non-array formula:

=LOOKUP(9.999E+307,$J$1:$J$11/($J$1:$J$11>=0))

Data is in J1:J11.

Regards,

 

[Mike H..]

Dear Team,

Kindly provide the formula to find the last positive value in given range even "zero" also.

Expected result is "Zero"

15,45,53,924.29
15,44,61,710.04
15,41,40,266.79
15,36,71,986.04
15,35,64,198.54
0.00
0.00
0.00
-200.00
-6,000.00
-1,00,000.00

Hi,

Try this

=LOOKUP(2,1/((A1:A20<>"")*(A1:A20>=0)),A1:A20)

 

[sguna1982]

Excellent!!!