Try this formula in D2 and copy it down

=IF(C21,IF(A2=A1,IF(D1="no","no","yes"),"yes"),"n o")


If this post helps click Yes
---------------
Jacob Skaria


"jjmore" wrote:

my spreadsheet looks something like this: (i have about 12000 rows)

A B C D
date depth change rate applies
01/01/03 1 .3 yes
01/01/03 2 .5 yes
01/01/03 3 1.0 no
01/01/03 4 .3 yes
01/01/03 5 .5 yes
02/15/03 1 .3 yes
02/15/03 2 .5 yes
02/15/03 3 .8 yes
02/15/03 4 .3 yes
02/15/03 5 .3 yes

in the 'applies' column, i currently have a formula =if(C31.0,"yes","no"),
which gives me the about result. However, I'm looking for a (additional?)
formula a bit more sophisticated, so that if column D has a "no", then the
following "yes" values for that same day are also "no"

the end result would look like this:
date depth change rate applies
01/01/03 1 .3 yes
01/01/03 2 .5 yes
01/01/03 3 1.0 no
01/01/03 4 .3 no
01/01/03 5 .5 no
02/15/03 1 .3 yes
02/15/03 2 .5 yes
02/15/03 3 .8 yes
02/15/03 4 .3 yes
02/15/03 5 .3 yes

any suggestions, advice, input would be greatly appreciated!