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...
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