Feb 08 2022 12:45 PM
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% |
Feb 08 2022 01:39 PM
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
Feb 08 2022 01:42 PM
=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)
Feb 08 2022 02:42 PM
Feb 08 2022 02:54 PM
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
Feb 08 2022 03:18 PM
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
Feb 08 2022 03:43 PM
Feb 08 2022 05:11 PM
what's your email address plse?
Feb 08 2022 05:28 PM
sorry im new to this platform, but how do I attach the excel worksheet plse?
Feb 09 2022 07:54 AM
@tana96799 In the message field you can expand the toolbar and attach files with the paper clip icon
Feb 09 2022 01:07 PM