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

Options

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.

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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".

• ✭✭✭✭✭✭
Options

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?

• Options

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

• Options

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

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

• Options

@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?!

• ✭✭✭✭✭✭
Options

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"), "")

• ✭✭✭✭✭✭
Options

Try this:

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!