determine positive or negative trend from infrequent values in a column

[djarum11]

i would like to generate the destination column using the source data, using simple IF statements and cell references. cells can reference cells below them, but not above. no arrays please if possible. the challenge is that there are an unknown number of zeros between the pos/neg 1's every time.


SOURCE DEST.
0 DOWN
0 DOWN
0 DOWN
0 DOWN
0 DOWN
1 DOWN
0 UP
0 UP
0 UP
0 UP
-1 UP
0 DOWN
0 DOWN
1 DOWN
0 UP
-1 UP

ETC.


thanks in advance.

[FDibbins]

And what would your expected answer look like?

[djarum11]

the destination column shows the result i would like to see: the word UP' or 'DOWN' based on the current sign of the source column. the source data is date-based with row1 having the most recent value. thanks

[FDibbins]

Sorry, I meant - show us what your expected outcome would like like?

[djarum11]

column B (DEST.) is my expected outcome. the formula, entered in B2, will be copied down col B. the formula will be something like if(a2=1,"DOWN", if(and(B3="DOWN", a2=0), "DOWN", if ....etc

[djarum11]

i think i have it:

if(a2=1,"DOWN", if(and(a2=0,B3="DOWN"), "DOWN", if(a2=-1,"UP", if(and(a2=0,B3="UP"), "UP","")))

when plugged into B2 and copied down i think this will work. i may have a typo in there.

basically there are 4 possible states i am checking for using the nested IFs. this should do it. will check tmrw
thank you