2

I have a data file of approximately 5000 repeated measures organized with rows containing IDs and repeated measures on weight, BMI, etc for children. I would like to find the maximum value of one variable (BMI) for each individual (out of up to 9 records). How can I do a lookup on multiple rows for each ID and return the max of a value for each person?

A very abbreviated example is as follows: HAVE:

ID  Date    BMI
1   1   20
1   2   18
1   3   24
2   4   23
2   5   19
2   6   17
3   7   25
3   8   18
3   9   21

WANT

ID  Highest BMI Corresponding date
1   24  3
2   23  4
3   25  7

Alternatively if there is a way to do this in SPSS or JMP (I don't have access to SAS now), please let me know. Thanks! Melissa

user000001
  • 32,226
  • 12
  • 81
  • 108
Melissa Melby
  • 21
  • 2

1 Answers1

1

You can do this easily in Excel in two parts

  • A PivotTable to extract the maxiumum BMI for each ID
  • matching the maximum BMI per ID to a date

Part 1 - PivotTable enter image description here

Create a PivotTable with

  1. A Row Label of ID
  2. Values as Max of BMI see below Part 2 - matching the date In the cell to the right of tge first BMI maximum, put this formula
    =SUMPRODUCT(--($A$2:$A$10=B14),--($C$2:$C$10=C14),$B$2:$B$10)/SUMPRODUCT(--($A$2:$A$10=B14),--($C$2:$C$10=C14))

(ensure you re-map your ranges if they differ from this example)

This formula the record that matches the ID and Max BMI

brettdj
  • 54,857
  • 16
  • 114
  • 177