compute sequence (partition) number

Topic

New Reply

I have a spread sheet with lead blood concentrations id, with blood sample dates, and blood lead levels in µg/dl. The patients are usually seen every three months if the blood concentrations merit being retaken if not the monitoring is stopped. I need to determine the sequence number of each patient based on the blood test test date, and the number of tests for each patient. In order to select all patients with 3 or more tests, and plot the blood levels by sequence test. I also need to compute the number days between test sequence 1 and test sequence 2 etc.

31730-sequence-number

Reply | Quote

Replies

In D2 The sequence number:
=COUNTIF($A$1:A2,A2)
Copy it down the column [Shouldnt the value in D9 be 3 and not 2? 2012-07-22 is the 2rd b value not the 2nd]

In E2 the total number of tests for the ID:
=COUNTIF([ID],A2)
Copy it down the column

In F2 the days from the last test for ID:
=IF(D2=1,””,SUMPRODUCT(([ID]=A2)*([test sequence number]=D2),[blood sample date])-SUMPRODUCT(([ID]=A2)*([test sequence number]=(D2-1)),[blood sample date]))

Steve

Reply | Quote