+ Reply to Thread
Results 1 to 8 of 8

Calculating percentage with 1 condition, and percentage of these who has 2. condition

  1. #1
    Registered User
    Join Date
    11-23-2020
    Location
    Copenhagen
    MS-Off Ver
    Office16
    Posts
    6

    Calculating percentage with 1 condition, and percentage of these who has 2. condition

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating percentage with 1 condition, and percentage of these who has 2. condition

    see if the attached helps, at all -- should 'translate' functions & delimiters per your locale.

    you could also consider using Power Query to transform your data in similar fashion but, hopefully this gives you something.

    one significant caveat - the current Diag.Key (Column H) is limited to 15 unique Diagnoses -- if you have more than this to deal with then the construct would require modification.

    to illustrate it working -- if you added a new "Diagnosis X" to C10 you should find a) your Diagnosis B stats change (i.e. 1 new 1+) and, b) "Diagnosis X" is added as a new row in the results table.
    Attached Files Attached Files

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Calculating percentage with 1 condition, and percentage of these who has 2. condition

    Another proposition with the hepler column. Then simple SUMPRODUCT could be used to calculate percentages.
    Like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    '
    See attached file
    Attached Files Attached Files
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    11-23-2020
    Location
    Copenhagen
    MS-Off Ver
    Office16
    Posts
    6

    Re: Calculating percentage with 1 condition, and percentage of these who has 2. condition

    Thank you! I am very much a novel in Excel, compared to you guys.

    I am no longer at work and do not have the dataset with me atm (it's confidential), but I think your construct would work, XLent.

    It is a very elaborate construct, that I would never have been able to do myself. Not really sure I actually understand what is going on in this construct, but it seems like it is working.

    In the real dataset I have exactly 15 different diagnoses, so the Diag.Key is perfect.

    However, in the real dataset I also have several hundred patients. So to use your construct on the dataset, I should expand the data areas covered in the key syntax, as well as in the "With" and "+# other" syntaxes? Perhaps if I remove column headers of the dataset I could just mark up the whole column? I.e.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for the key syntax? Or perhaps marking the whole (infinite) column for calculation is a bad idea for the sake of my CPU memory?

    Again, thank you. Your help is very much appreciated.

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating percentage with 1 condition, and percentage of these who has 2. condition

    Hi there, given the fact your diagnoses are fixed and you have only a few hundred rows to deal with I would suggest using Kaper's approach in preference to mine, it's much simpler and will achieve the same outcome
    note: you might need to tweak the =COLUMNS($D:D) to >=COLUMNS($C:D) depending on requirement re: counts of 1+ cases etc

    In either case, as you state, you will need to modify your range references (w.r.t rows 2:11) to account for your real-life data but, yes, avoid using entire column references (i.e. B:F)
    Last edited by XLent; 12-01-2020 at 02:05 PM. Reason: edit: reworded

  6. #6
    Registered User
    Join Date
    11-23-2020
    Location
    Copenhagen
    MS-Off Ver
    Office16
    Posts
    6

    Re: Calculating percentage with 1 condition, and percentage of these who has 2. condition

    Hi. Again, thank you for your help! It is really appreciated. I can not do much else than thank you, and maybe you can find comfort in that your help, helps us get a better overview and understanding of the data, which in the end might help some of our cancer patients in their rehabilitation process.

    I could not get the numbers from Kaper's approach to match what actually was shown in the example, but it seems like Xlent's "tweak" fixed the problem for 'with 1' cases. However, I still had problems with the rest, i.e. 'with 2', 'with 3' etc.
    I have to admit I do not understand the approach, but I think I fixed the problem by trial and error and looking for a pattern - as we say in Denmark: "Even a blind hen can find grain".

    I have attached my "fix" to Kaper's approach. Is this "fix" correct?
    Attached Files Attached Files
    Last edited by Knucle; 12-03-2020 at 05:10 AM. Reason: Gramma

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating percentage with 1 condition, and percentage of these who has 2. condition

    Hi, your fix will work but, you should find (with your latest file) that if you copy the formula in cell D15 and paste it across the matrix D15:G20 you will get the exact same results.

    by using a mix of absolute and relative references you can increment "n" for the >= test using a common formula - i.e. no need to modify the formula in each column

    to illustrate concept:

    D30: =COLUMNS($C:D)
    will return 2

    if you copy this formula to E30 you will get 3, and you should see the formula has changed to:

    E30: =COLUMNS($C:E)

    i.e. the C reference is anchored (c/o $) but the 2nd column reference has extended by one as, unlike C, the reference to D was not 'anchored'

    and so, of course, as you keep copying to the right so the result will increase by 1 each time

    Hope that helps clarify.
    Last edited by XLent; 12-03-2020 at 05:17 AM. Reason: reworded narrative

  8. #8
    Registered User
    Join Date
    11-23-2020
    Location
    Copenhagen
    MS-Off Ver
    Office16
    Posts
    6

    Re: Calculating percentage with 1 condition, and percentage of these who has 2. condition

    It does - thank you very much! I should have realized that myself - it is a simple excel feature. I had myself confused trying to understand how the SUMPRODUCT function with a helper column could be used in this way to solve my initial problem.
    Last edited by Knucle; 12-03-2020 at 05:45 AM. Reason: rewording

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple If Condition with Percentage
    By sg2209 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-07-2018, 12:31 PM
  2. Separate Percentage condition required
    By Saikrishna12345 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2018, 11:01 PM
  3. Finding percentage when a condition is met
    By Adamlee in forum Excel General
    Replies: 3
    Last Post: 03-10-2015, 01:05 PM
  4. Replies: 0
    Last Post: 02-18-2015, 04:45 PM
  5. calculating percentage of percentage
    By zinneken in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-10-2013, 10:12 AM
  6. Replies: 11
    Last Post: 12-30-2012, 01:47 PM
  7. Calculating a percentage with the end percentage in mind
    By Shadowshady in forum Excel General
    Replies: 2
    Last Post: 06-17-2006, 04:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1