Apr 20 2022 09:22 PM
Hello,
I was wondering if it is possible to use multiple conditional statements in Excel to display data from a dataset .
For example, I have a dataset about demographic characteristics (e.g. age, education, job sector). Using the dataset, is there a function where I am able to display the percentage of people that are:
1. 30 years old AND
2. Completed university AND
3. Work in health care
Furthermore, can I mix and match condition statements?
Thank you in advance!
Apr 21 2022 12:49 AM
In the following, I'll assume that you have a table named Table1 with columns Person, Age, Education and Employment.
The formulas are:
G5: =COUNTIFS(Table1[Age],G1,Table1[Education],G2,Table1[Employment],G3)
G6: =COUNTA(Table1[Person])
G7: =G5/G6
G7 has been formatted as Percent (with 1 decimal place).
You can refer to specific ranges instead of to table columns of course.
Apr 26 2022 02:10 PM
Hi, thank you for taking the time to respond. I have a follow-up question for this as the dataset is more complex. The end goal is to have an interface where users can select as many variables from a raw dataset and see the percentage of respondents in the data that meet all their selected conditions (similar to filtering by multiple columns in Excel but in a more user-friendly way).
Using the previous example, what if there were 50 more variables in addition to age, education and employment sector (e.g. job position, hours worked per week, salary range, etc). In addition, each variable has multiple response options and multiple respondents. Is it possible to mix and match each response options across all variables? I image the formula you provided would not work as there are many possible response combinations across all variables.
Examples of combinations:
Person 1: Age 25 + college education + works in healthcare + works full time + works 40 hours/week + ....
Person 2: Age 25 + college education + works in skilled trades + works full time + works 40 hours/week + ...
Person 3: Age 40 + university education + works in healthcare + works part time + works 20 hours/week + ...
How many people responded like person 1? Or person 2? And so on.
I hope this makes sense. Thank you again for the help!
Apr 26 2022 02:15 PM
It would be a lot of work to build an interface for that, and I imagine it would end up looking much like the built-in filter options...