Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
  •