I have a matrix (spreadsheet) of patients who can have up to 5 diagnoses of several different diagnoses.
I have the patients in the rows and in the columns I have 1. diagnosis, 2. diagnosis etc. in which several different diagnoses (A-F) can be assigned (see attached example spreadsheet).
The diagnoses come in random order.
I would like to calculate how big a percentage who has diagnosis A of the total number of patients, and then I would like to calculate how big a percentage (of total) who has diagnosis A and 1 other diagnosis, and how many who has diagnosis A and 2 other diagnoses etc..
The same for diagnoses B, C, etc.
Thus, getting a summary who shows something like this:
Diagnosis A: 45% of total population.
Diagnosis A with 1 other diagnosis: 20 % of total population
Diagnosis A with 2 other diagnoses: 5 % of total population
Diagnosis A with 3 other diagnoses: 1 % of total population
Diagnosis B: e.g. 10 % of total population.
Diagnosis B with 1 other diagnosis: 8 % of total population
etc.
I cannot get my head around how to do this.
Is this somehow possible to do using Pivottable or the countifs function?
I would really appreciate your help, thank you!
Bookmarks