New Contributor

# 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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