Algorithm to code errors

**cls1** · 09-25-2008, 07:04 AM

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

**Cheeky Charlie** · 09-25-2008, 07:16 AM

Should F47 be ch or m?

**arthurbr** · 09-25-2008, 07:19 AM

I think "m" Charlie, sum of 1's is 5 not 6

**Cheeky Charlie** · 09-25-2008, 07:23 AM

If column D = 0 after the previous six responses have been 1

I could have sworn that said 4 a minute ago! I think a truth table is in order?

**Cheeky Charlie** · 09-25-2008, 08:01 AM

Urk!

=IF(D2=0,IF(ISERROR(INDIRECT("A"&ROW()-6)),"oth",IF(OR(I1="ch",I1="p"),"p",IF(COUNTIF(INDIRECT("D"&ROW()-6&":D"&ROW()-1),1)=6,"ch","p"))),"")

oh no wait, I haven't put m in yet...

**Cheeky Charlie** · 09-25-2008, 09:05 AM

In Column F:

=IF(D2=0,IF(OR(F1="ch",F1="p"),"p",IF(ISERROR(INDIRECT("A"&ROW()-6)),"oth",IF(COUNTIF(INDIRECT("D"&ROW()-6&":D"&ROW()-1),1)=6,"ch",IF(ISODD(COUNTIF(F$1:F1,"ch")),"m","oth")))),"")

A UDF may be more appropriate.

The above is for "alternating" changes, this is for "once changed - always active":

=IF(D2=0,IF(OR(F1="ch",F1="p"),"p",IF(ISERROR(INDIRECT("A"&ROW()-6)),"oth",IF(COUNTIF(INDIRECT("D"&ROW()-6&":D"&ROW()-1),1)=6,"ch",IF(COUNTIF(F$1:F1,"ch")>0,"m","oth")))),"")

I enjoyed that

HTH

edit(2):-
Copy formula into D2 and copy down

**cls1** · 09-25-2008, 09:08 AM

I have no idea what all that means, or what a UDF is (!) but thanks very much! I'll try and implement it now.

**arthurbr** · 09-25-2008, 09:16 AM

A UDF stands for User defined Function.
It is a function created with VBA by a user, which afterwards can be used as a usual function in XL.
If you click the functions button in the toolbar and select the "User defined" category you will find the UDF if any