# Thread: compute sequence (partition) number

1. ## compute sequence (partition) number

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.

sequence number.JPG

2. 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

3. ## The Following User Says Thank You to sdckapr For This Useful Post:

r3x3 (2012-08-14)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•