I am trying to get the percentile of responses that return "1-2" if the responded "4 Months" in

Brittany Sheppard

I am trying to get the percentile of responses that return "1-2" in a column if the response is "4 Months" in another column.

=PERCENTILE(COLLECT({Preceptors}, {Preceptors}, ="1-2", {Survey Time}, "4 Months", 0.5))

or =PERCENTILE(COLLECT({Preceptors}, {Preceptors}, ="1-2", {Survey Time}, {Survey Time}, "4 Months", 0.5))

Both return incorrect arguments.

Carson Penticuff

Are you sure you are looking for the "percentile" or maybe just the percent? If you want a percentile, you will need a numerical column as the source.

Is it the case that you want to know what percent of responses that list "4 Months" also list "1-2" in the other column?

Paul Newcome

You need to close out the COLLECT function before entering the second field of the PERCENTILE function. Try your first one again, but move one of the closing parenthesis from the very end to immediately after "4 Months".

Carson Penticuff

Are you sure you are looking for the "percentile" or maybe just the percent? If you want a percentile, you will need a numerical column as the source.

Is it the case that you want to know what percent of responses that list "4 Months" also list "1-2" in the other column?

Brittany Sheppard

@Carson Penticuff Yes, I am trying to get the percentage of responses that list "4 months" and "1-2" in the other column.

Brittany Sheppard

@Paul Newcome This gives me #Incorrect Arguments when using this formula:

=PERCENTILE(COLLECT({Preceptors}, {Preceptors}, ="1-2", {Survey Time}, "4 Months"), 0.5)

Brittany Sheppard

@Carson Penticuff I changed the formula to this»=AVG(COLLECT({Preceptors}, {Preceptors}, ="1-2", {Survey Time}, "4 Months"), 0.5), and I got an answer, but I'm not sure if it's the correct answer. How do I know?!

Carson Penticuff

Try this and see if it what you are looking for:

=IF(COUNTIFS({Survey Time}, "4 Months") > 0, COUNTIFS({Survey Time}, "4 Months", {Preceptors}, "1-2") / COUNTIFS({Survey Time}, "4 Months"), "")

Paul Newcome

Try this:

=IFERROR(COUNTIFS({Survey Time}, @cell = "4 Months", {Preceptors}, @cell = "1-2") / COUNTIFS({Survey Time}, @cell = "4 Months"), 0)