CODEWITHSUNDEEP
excel
zdsnide
zdsnide

Reputation: 51

Percentage value is used based on other column values

I have a table with an Appointment ID, code, and lab text. I want the fourth column to display the percentage of how often the text field is used based off the code column per appointment.table

For example, whenever a code of 123 is used, (it's used on 2 unique appointments) 'lipid panel' Lab Text is present both times. So i want 'lipid panel' to be 100% whenever 123 is used. 'Occult Blood' is used on only 1/2 appointments where there is a code of 123, so I want it to be 50%

Upvotes: 0

Views: 58

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use this:

=(COUNTIFS(B:B,B2,C:C,C2,A:A,"<>"&A2)+1)/(SUMPRODUCT(($B$2:$B$10=B2)/(COUNTIFS(B:B,B2,A:A,$A$2:$A$10)+($B$2:$B$10<>B2))))

enter image description here

Upvotes: 1

Related Questions