Forum Discussion
Data output from multiple conditions
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!
3 Replies
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.
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!
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...
