Algorithm to code errors

Hi,

I'd like to know whether there's a way to develop an algorithm to code errors automatically using excel. The sheet attached shows what I've done by hand (see column F). If column D = 0 after the previous six responses have been 1, then F should be coded as a 'changeover' response (currently coded as ch, but can be numerical and recoded later if necessary.) If the responses immediately afterwards also = 0, then these should be coded as 'perseverative'. Responses that = 0 between changeover responses but not IMMEDIATELY after them should be coded as 'memory' errors. Any other errors (i.e. errors that occur before changeover responses) can be coded as 'other').

If anyone has any ideas, I'd be very grateful,

Thanks