Calculating a difference for lab results for only those with more than one result

Copper Contributor

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

A2/27/20205.6%
A9/10/20215.7%
B6/8/20209.1%
B9/26/202010.5%
B3/27/202111.2%
B9/3/202112.7%
C8/13/20205.2%
10 Replies

@cklingel 

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

@cklingel 

=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)

 

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

@OliverScheurich 

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

tana96799_1-1644360471592.png

 

@cklingel 

fyi im using xl v.2013. here's what the data and the rpt looks like. you'll have to do minor calc on the side but it's really easy...not sure if you wanted to subtract the 1st lab test from the last or vice versa, in my rpt your subtracting the 1st lab result from the latest

tana96799_0-1644361863513.png

 

Hello,
Yes, this is exactly what I am looking for. Thank you!

@cklingel 

sorry im new to this platform, but how do I attach the excel worksheet plse?

@tana96799  In the message field you can expand the toolbar and attach files with the paper clip icon

cklingel_0-1644422046065.png

 

@cklingel Hi, i just sent it to your email address plse let me know

 

Thanks