How to display the last positive or negative value of column when zero value is ignored?

English
#1

hello guys, I need to ask you for help. I have positive, negative and also zero values in columns A. Now, in column B1 I need to display the last known value (positive or negative) of column A, but not zero. Here is an example:

A1: 100
A2: 0
A3: -200
A4: 0

So in column B1 should be displayed value “-200” in this case (because 0 value should be ignored).
I tried to use this =INDEX(A:A;COUNT(A:A)+1) but it displays the last value including zero.

Any ideas how to fix this?
Thank you very much.

#2

What about =INDIRECT("A"&SUMPRODUCT(MAX(ROW(A:A)*(A:A<>0))))?

#4

Thank you very much for your help. It does work!

#3

try:
=INDEX(A1:A1000;MAX(IF(A1:A1000<>0;ROW(A1:A1000);"")))
enter with [ctrl]+[shift]+[enter] or the with the array|matrix-option in Formulawizard

#5

From some reason this one gives me an error:504, but the previous solution works, so that is enough for me. Anyway, than you for your time!

#6

some reason is exactly: you do not enter as array-formula

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.