How to combine two disjoint ranges in an array formula

Question

How can I combine two disjoint ranges to be used in an array formula.

Example:

{=PERCENTILE(10^((E2:F2)/10),0.7)}

This works however:

{=PERCENTILE(10^((E2:F2,N2:P2)/10),0.7)}

This will fail. How can I get this functionality to work in excel?

Answer 1

This is an old question, but since this is what comes up in Google searches for disjoint ranges: newer excel versions support joining disjoint ranges using colon:

{=PERCENTILE(10^((E2:F2:N2:P2)/10),0.7)

You can add a colon and another start:end style range, as many times as you want.