1

I have a dataset

~2000 patients are sampled weekly for 18 months and get a positive or negative results (or something indeterminate)

each patient has a single row the columns are the results or test at each week

I would like a column at the end that contains the column when the test result first goes from positive to negative

e.g. week          1      2      3     4      5     6    7     8 ......
     patient 1     pos    pos    pos   neg    neg  pos   neg   neg  ...
     Patient 2     pos    pos    pos   pos    pos  pos   neg   neg  ...

so the column at the end would say patient 1 became negative at week 4 patient 2 became negative at week 7

I would then like another column which states when a patient has two consecutive negative tests, e.g. patient 1 has 2 negatives from week 4, so the value in the column would be week 4, and patient 2 would be week 7

I will then use this to create Kaplan Meier plots

Can anyone help?

1
  • Welcome to Super User. We are not a script writing service. We expect users to tell us what they have tried so far (including any scripts they are using) and where they're stuck so that we can help with specific problems. Questions that only ask for scripts are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?.
    – DavidPostill
    Nov 10, 2015 at 22:21

1 Answer 1

0

The two formula you need:

  • to learn when data turned to negative:
    =SMALL(IF(($B2:$I2="pos")*($C2:$J2="neg"),$C$1:$J$1,""),1)
  • double negative tests:
    =SMALL(IF(($B2:$I2="neg")*($C2:$J2="neg"),$B$1:$I$1,""),1)

Both formulas are array formulas, so you need to enter them by CTRL+SHIFT+ENTER.

When you create your formulas, you need to be very careful to include ranges with the same number of cells.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .