Forum Discussion
Calculating a difference for lab results for only those with more than one result
Hello,
Is there a way to calculate the difference in values for an individual only if there is more than one value for a particular referred value?
For example I have the test results for over 1000 patients listed by patient ID, date and lab result. I only want to calculate the difference in the first lab result versus the last lab result but some patients only have one lab value for that time period, therefore there is no difference to calculate. Below, patientID A has two values so I would want the difference between the two dates, Patient B has multiple values so would want the difference between the first and last date, and Patient C only has one value so I can't calculate a value. Is there a way to account for this?
Thank you.
patientid labdate labvalue
| A | 2/27/2020 | 5.6% |
| A | 9/10/2021 | 5.7% |
| B | 6/8/2020 | 9.1% |
| B | 9/26/2020 | 10.5% |
| B | 3/27/2021 | 11.2% |
| B | 9/3/2021 | 12.7% |
| C | 8/13/2020 | 5.2% |
10 Replies
=XLOOKUP(D2,$A$2:$A$8,$B$2:$B$8,,,-1)-XLOOKUP(D2,$A$2:$A$8,$B$2:$B$8,,,)This formula works as shown in my spreadsheet.
In D2 i entered the formula:
=UNIQUE(A2:A8)Here's the my data table using your data if that's what you want then let me know i'll send over the formulas
- Hello,
Yes, this is exactly what I am looking for. Thank you!
- Thank you for your quick help. When I run the D2 formula I received #SPILL errors so after correcting received values not consistent with what they should be. For instance, Patient A had two results so his result difference should have been 0.1% but I am getting 561?
patientid labdate labvalue Column1
A 2/27/2020 0.056 37647 561.00
A 9/10/2021 0.057
Alternatively, in D2:
=IF(AND(B2=MAXIFS($B$2:$B$7000,$A$2:$A$7000,A2),COUNTIF($A$2:$A$7000,A2)>1),INDEX($C$2:$C$7000,MATCH(MAXIFS($B$2:$B$7000,$A$2:$A$7000,A2),$B$2:$B$7000,0))-INDEX($C$2:$C$7000,MATCH(MINIFS($B$2:$B$7000,$A$2:$A$7000,A2),$B$2:$B$7000,0)),"")
Adjust the ranges if you have more than 7000 rows, then fill down
