3

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?

eng3
  • 431
  • 3
  • 18
  • It's seeming like your exponentiation is where this breaks down. You can do exponentiation in an array formula, but not with disjointed ranges. – Kyle Jun 06 '17 at 20:55
  • Yes I can do it with a continuous range but not a disjoint range. I would like to know how I can achieve this with a disjoint range – eng3 Jun 06 '17 at 21:09
  • worth doing a UDF for the whole first parameter expression maybe... – MacroMarc Jun 06 '17 at 22:18
  • How would I do a UDF. I tried a union but that did not seem to work – eng3 Jun 06 '17 at 22:31
  • For other Googlers, seems like the [comma operator is the right answer when it works (which is not always unfortunately)](https://superuser.com/a/447560/10136), and unlike [Google sheets](https://stackoverflow.com/q/10793321/1026), Excel [doesn't allow](https://stackoverflow.com/q/46002409/1026) to construct arrays from cell references (i.e. `SUM({E2:F2,N2:P2})`) – Nickolay Apr 26 '21 at 08:05

1 Answers1

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.

JonB
  • 350
  • 1
  • 7