need formula help

[jjmore]

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(C3<1.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!

 

[Tom Hutchins]

Try this in D2, and copy down as needed:

=IF(AND(D1="no",A2=A1),"no",IF(C2<1,"yes","no"))

Hope this helps,

Hutch

 

[Jacob Skaria]

Try this formula in D2 and copy it down

=IF(C2<1,IF(A2=A1,IF(D1="no","no","yes"),"yes"),"no")


If this post helps click Yes